declaring string - 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

declaring string

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-03-15 : 02:51:57

hi,

i have follwing situation:

declare @string_of_products varchar(8000)
set @string_of_products = 'ID12','ID13','ID14'
-- or
-- set @string_of_products = '''ID12'',''ID13'',''ID14'''

select * from tbl_products
where product_id in (@string_of_products)

all productIDs are varchar datatype which does not allow me to create a string as shown in example. is there a workaround?

thank you

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-15 : 03:18:33

declare @string_of_products varchar(8000)
select @string_of_products = product _id from tbl_products-- or
-- set @string_of_products = '''ID12'',''ID13'',''ID14'''

select * from tbl_products
where product_id in (@string_of_products)

this might work....

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-03-15 : 03:34:52

unfortunatelly it does not work. only takes last product in a row.

sumitxmahajan
Starting Member

2 Posts

Posted - 2011-03-15 : 03:54:48

Slimt,

It does work as already posted for you by ahmeds08. Please see it more carefully. I am changing your code a bit to demonstrate it to you. Please revert it completely to your code style.

declare @string_of_products varchar(8000)
set @string_of_products = '''ID12'',''ID13'',''ID14'''
DECLARE @vSQL VARCHAR(MAX)
SET @vSQL = 'select * from tbl_products where product_id in ('+@string_of_products+')'
PRINT @vSQL

In this case we are capturing the SQL to a variable to prove it. Run the above SQL and see it for yourself.

Thanks!
Sumit M.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-03-15 : 04:15:58

sumitxmahajan,

creating dynamic sql and executing query with sp_executesql is not a solution.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-15 : 05:10:52

Have a look at:

http://www.sommarskog.se/arrays-in-sql-2005.html

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-03-15 : 06:16:34

In a relational database different objects should be stored on different rows in a table. Please don't use dynamic SQL as one person has suggested unless you want a real headache. Instead create a table variable and then the query is easy:


DECLARE @StringOfProducts TABLE(product_id varchar(50) Primary Key)

INSERT INTO @StringOfProducts 
SELECT 'ID12'
UNION ALL
SELECT 'ID13'
UNION ALL
SELECT 'ID14'

SELECT * FROM tbl_products
WHERE product_id in (SELECT * FROM @StringOfProducts)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources