pass several parameters to sp - 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 2005 Forums

Transact-SQL (2005)

pass several parameters to sp

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-03-01 : 03:50:43

Hi

I currently build a sql query based on how many BookID items a user have selected, kinda like this..

"Select * from tbl_test where BookID = 1 or BookID = 2" and so on...

Then I execute the built query to the database, but I would like to be able to just pass on the BookID parameters. Can that be done?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-03-01 : 04:08:32

try this

Declare @BookIds Varchar(8000)
select @BookIds = '1,2,3,4,5'

Select *
from tbl_test
where ','+@BookIds+',' LIKE ',%'+CAST(bookid AS VARCHAR(32))+'%,'

Store the Bookids in the variable with comma separated

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-03-01 : 05:37:40

Hi

Thanks, if I wanted to use this on a nVarchar column would this then be correct?

Declare @BookIds Varchar(8000)
select @BookIds = '''1'',''2'',''3'',''4'',''5'''

Select *
from tbl_test
where ','+@BookIds+',' LIKE ',%'+CAST(bookid AS VARCHAR(32))+'%,'

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-01 : 23:47:42

check this example with sample data
if u use '1,2,3,4' or '"1","2","3","4"'

Declare @BookIds NVarchar(4000)
select @BookIds = '"1","2","3","4","5"'
--select @BookIds = '1,2,3,4,5'

Select *
from ( select 1 as bookid, 'kk' as name
union all
select 2 , 'ker'union all
select 3 , 'sdafs'union all
select 7 , 'fdredr')t
where ','+@BookIds+',' LIKE ',%'+CAST(bookid AS NVARCHAR(32))+'%,'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources