SQL IN Clause Limitations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SQL IN Clause Limitations

Author

Topic

premalm
Starting Member

3 Posts

Posted - 2014-04-24 : 17:12:56

I have a SQL with an IN clause which is run in a stored procedure.

IN CLAUSE = 'A321', 'A2452', 'AhkjsdfO', 'Aldkjfds_Ddsfd', 'Adsf_sdfd', 'CDO', 'Csdfd', 'Msddf', 'Tdsf_sdfds', 'Wsdf_sdfd', 'Whdsdfdd'

When I run the SQL with the IN clause it runs fine but when I pass the entire IN clause as a parameter to the stored procedure its cuts the IN clause. 'A321', 'A2452', 'AhkjsdfO', 'Aldkjfds_Ddsfd', 'A

Any Ideas ?
Thanks.
Premal.

pm

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-24 : 17:33:39

Look up the stored parameter definition to see what the length of the paramter is. Might be something like VARCHAR(50). If so you may need to increase that.

It also could be getting truncated from the calling program. If you are using C#, for example, and defined the parameter with a shorter length than required this can happen.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-25 : 07:59:58

Are you trying to do something like this:


exec myproc @inclause='(''A321'', ''A2452'', ... etc)'

the, in the proc:


select ... where column in @inclause

That won't work. You can't pass a list of values to a proc like that.

premalm
Starting Member

3 Posts

Posted - 2014-04-25 : 09:45:11

gbritton. Yes, we can pass a list of values to a proc like that. My procedure has 2 parameters.

exec myproc "'A321', 'A2452', ... ", "'A321', 'A2452', ... "

Im my case the problem was with the length of the parameter in the stored proc definition which James K has pointed.

Thanks for all the help.

pm

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-25 : 12:51:26

yes, you can pass a list of values like that. However you can't just plop them into an IN clause as is. You'll have to parse them out into individual variables. e.g. this won't work:


create proc myproc @list varchar(max) as
print @list
select '1' where cast('A321' as varchar(20)) in (@list)
GO
exec myproc @list="'A321', 'A2452', 'A1234' "

It will always return no rows.

Or, are you doing dynamic SQL in the proc (you didn't say)

premalm
Starting Member

3 Posts

Posted - 2014-04-25 : 14:39:53

I am using dynamic SQL in the procedure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources