Parameterized query with a list of strings

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

General SQL Server Forums

New to SQL Server Programming

Parameterized query with a list of strings

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2012-09-21 : 13:04:33

Hi,

I have a query like this

Select fcp from person where OrgCode in ('1111', '2222')

I want to build the list of strings in my VB code, and pass it through a parameter, like this:

Select fcp from person where OrgCode in (@OrgCodes).

I have a very long query which I've shortened below:

exec sp_executesql N'SELECT FCP FROM [person] where
OrgCode in (@OrgCodes)
',N'@Orgcodes=N'''1111'', ''2222'''

It's not working. It returns nothing, where the query without parameters, does return results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 13:10:09

in wont work like that

make query like


Select fcp from person where ',' + @OrgCodes + ',' like '%,' + cast(OrgCode as varchar(10)) + ',%'

or use a string parsing udf based logic like

see scenario 4

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

see parse udf here

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 13:12:15

To use dynamic SQL like you are doing you will have to compose the query string including the parameters and then run it as a dynamic SQL. However, that is very unsafe because of SQL injection risks. The two alternatives I can think of are:

a) compose your query like this:

SELECT FCP
FROM   [person]
WHERE  ','+@OrgCodes+',' LIKE '%,'+CAST(OrgCode AS VARCHAR(32)) + ',%';

b) Use a splitter function (such as the one described in Jeff Moden's article here - see Fig 21 of the article: http://www.sqlservercentral.com/articles/Tally+Table/72993/) Then your query would be:

SELECT FCP
FROM Person p
WHERE Orgcode IN (SELECT Item FROM MASTER.dbo.DelimitedSplit8K(@OrgCodes,',') )

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2012-09-21 : 13:18:04

quote:
Originally posted by visakh16

in wont work like that

make query like

Select fcp from person where ',' + @OrgCodes + ',' like '%,' + cast(OrgCode as varchar(10)) + ',%'

SQL Server MVP
http://visakhm.blogspot.com/

Thank you. With some modifications, this did what I wanted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 13:23:54

please keep in mind that it may hurt performance when working over larger dataset due to the data convertion logic involved

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources