Problem from IN Clause when passing a value

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

Problem from IN Clause when passing a value

Author

Topic

Jakespeed
Starting Member

2 Posts

Posted - 2009-08-12 : 03:13:35

Hello SQL Team! I have problem from a gridview.
I created a query that would select multiple ID (like 201,306, 409). Here's my sql transact...

SELECT * FROM cms_property
WHERE property_id IN (@property_id)
Note: property_id is an Integer...

Scenario: If I will put values to a textbox for example ID's separated by comma to pass that value to @property_id and the result that im getting is invalid value.... How can I put or cast a proper format for '@property_id'?

Please help

Jake

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-12 : 03:16:30

see
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sommarskog.se/arrays-in-sql-2005.html

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-12 : 03:16:41

You must use Dynamic Sql

@statement varchar(500)
@property_id varchar(50)

set @property_id='1,2,5,3,6'

set @statement ='select * from table where id in ('+@property_id+')'

exec (@statement)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-12 : 03:17:17

@property_id like '%CAST(property_id AS varchar(max))%'

examples like
select * from tablename where '%,' + @property_id + ',%' LIKE '%,'+ CAST( property_idAS vARCHAR(255)) +',%'

exec('select * from tablename where cast(property_idas varchar) in('+@property_id+') ')

select * from tablename where patindex('%,' + CAST( property_idAS VARCHAR(255)) +',%',','+@property_id+',' )>0

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-08-12 : 03:18:40

SELECT * FROM cms_property
WHERE '''' + replace(@property_id,',',''',''') + '''' LIKE '%''' + property_id + '''%'

Rahul Shinde

Jakespeed
Starting Member

2 Posts

Posted - 2009-08-12 : 03:49:50

Thank you for all that came to help me!!! I tried Mr. Khtans suggestion for providing the link to http://www.sommarskog.se/arrays-in-sql-2005.html. Anyway I Mr. Senthil script also worked fine... Anyway thank you again.

Kind Regards,

Jake

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources