string parameter with comma - 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

General SQL Server Forums

New to SQL Server Programming

string parameter with comma

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2014-03-20 : 16:16:12

Hi there,
I want to bring a string parameter to where clause like this:

select .. from ...
Where Code IN (@Code)

When @Code is 'ABC' it works but when @code is 'ABC, XYZ' it won't work. I know for IN statement we should use IN ('ABC', 'XYZ') and I tried make @code = '''ABC'', ''XYZ''' still not working. I tried

Print '''ABC'', ''XYZ'''

I got 'ABC', 'XYZ' but why it does not work in @Code?
Thanks in advance.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-20 : 16:47:49

You can't do that. In order to do what you are trying to need to use Dynamic SQL, split/parse string function or a "funky" LIKE/PATINDEX comparison.

What you should be using is called a TVP (table-valued parameter):
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/
http://technet.microsoft.com/en-us/library/bb510489.aspx

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-21 : 00:47:13

It is not possible to use 'IN Clause' in that Fashion

The below answers your Question....

DECLARE @Id VARCHAR(100) = '25,107'
Select SUBSTRING(@ID ,PATINDEX('%,%',@ID)+1 ,(LEN(@ID))) AS FirstNumber
SElect SUBSTRING(@ID ,0,PATINDEX('%,%',@ID)) AS SecondNumber

Now you are able to use Those 'First Number' and 'Second Number' in your where clause.........

Murali Krishna

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-21 : 01:03:14

quote:
Originally posted by MuralikrishnaVeera

It is not possible to use 'IN Clause' in that Fashion

The below answers your Question....

DECLARE @Id VARCHAR(100) = '25,107'
Select SUBSTRING(@ID ,PATINDEX('%,%',@ID)+1 ,(LEN(@ID))) AS FirstNumber
SElect SUBSTRING(@ID ,0,PATINDEX('%,%',@ID)) AS SecondNumber

Now you are able to use Those 'First Number' and 'Second Number' in your where clause.........

Murali Krishna

It can usefull for passing two values in string parameter

not for multi values in string parameter.....

DECLARE @Id VARCHAR(100) = '1235,346,136,1643,8513'
Select SUBSTRING(@ID ,PATINDEX('%,%',@ID)+1 ,(LEN(@ID))) AS FirstNumber
SElect SUBSTRING(@ID ,0,PATINDEX('%,%',@ID)) AS SecondNumber

Veera

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-21 : 11:32:02

I don't know if it is worth mentioning or not that Veera's solution cannot make use of an index seek, which may or may not be important in your case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources