Where 'IN' Query Parameter problem

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

Where 'IN' Query Parameter problem

Author

Topic

mohsinjk
Starting Member

1 Post

Posted - 2010-08-02 : 04:42:49

Hello,

I am trying to send multiple values in one query using <Where clause "In(@ID)">

@ID varchar(max)
Select * from tblProduct
WHERE tbl_PRIM_Sales.Sale_ID in (@ID)

my logic is
@ID could be
@ID=47 this is ok
but when I pass value in a parameter something like this.. @ID='47,58,98'

It gave m conversion error
'Conversion failed when converting the varchar value '47,58' to data type int.'

Please give me some hints

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 04:53:34

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-02 : 04:54:05

Hello,

You must use dynamic SQL for this task:

declare @ID varchar(max)
declare @sql varchar(max)

set @sql = 'SELECT * FROM tblProduct WHERE tbl_PRIM_Sales.Sale_ID in ('+char(39)+@ID+char(39)+')'
EXEC (@sql)

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 06:19:52

"You must use dynamic SQL for this task"

Better to sue a Spliter Function. Much faster for large numbers of IDs, and no risk of SQL injection, more likely to give the expected outcome if the @ID list contains any NULLs

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-08-02 : 09:17:28

try like this too
select * from emptable where '%,' + @ID+ ',%' LIKE '%,' + CAST( empid AS VARCHAR(255)) +',%'

exec('select empid,sum(empsal) from emptable where cast(empid as varchar) in('+@ID'+') group by empid')

select * from emptable where patindex('%,' + CAST( empid AS VARCHAR(255)) +',%',','+@ID+',' )>0

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 09:53:40

Although they will be slow if [emptable] has a large number of rows, or @ID list is large. EXEC example has risk of SQL Injection.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-03 : 04:13:24

quote:
Originally posted by Kristen

Although they will be slow if [emptable] has a large number of rows, or @ID list is large. EXEC example has risk of SQL Injection.

Now-a-days, If I have to write such queries vulnarable to SQL Injection, I use this approach
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources