Passing comma delimited strings in an SP for a where in ('") 1

[CJwater]

Hi,

I'm having trouble creating/running an SP that accepts two string parameters that will be used in a where in clause. Below is how I'm going about it and the error I'm getting. Would like to resolve the error but if someone has a better way to do this I'm all for it! (I try to avoid string queries)

----- SP -------
DECLARE @strSQL as varchar(1000), @a varchar (400), @b varchar (400)
set @a =@ComCd
set @a=replace (@a, ',' , ''',''')

set @b =@group
set @b=replace (@b, ',' , ''',''')


select @strSQL = "SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc FROM vw_IMITMIDX_GroupingFields as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat Where VW.Prod_Cat < '990' and Commodity_Cd in ( '"+@a+ "' ) and PO_Super_Cat in ('" +@b+ "')"

--print (@strSQL)
execute @strSQL
GO
----------- END SP ---------

How I'm executing it

exec Web_GetVendorCat 'elx,frg', 'cooking,LAUNDRY'
----------------------------
The error I'm getting:
-- PS IF I RUN THE FIRST LINE of code RETURN IN QUERY ANALYZER IT WORKS I'm not following the error "is not a valid identifier" something to do with the quotes???

SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc FROM vw_IMITMIDX_GroupingFields as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat Where VW.Prod_Cat < '990' and Commodity_Cd in ( 'elx','frg' ) and PO_Super_Cat in ('cooking','LAUNDRY')

Server: Msg 203, Level 16, State 2, Procedure Web_GetVendorCat, Line 22

The name 'SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc FROM vw_IMITMIDX_GroupingFields as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat Where VW.Prod_Cat < '990' and Commodity_Cd in ( 'elx','frg' ) and PO_Super_Cat in ('cooking','LAUNDRY')'

is not a valid identifier.


PS: -- IF I RUN THE FIRST PIECE RETURNED IN QUERY ANALYZER IT WORKS
SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc FROM vw_IMITMIDX_GroupingFields as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat Where VW.Prod_Cat < '990' and Commodity_Cd in ( 'elx','frg' ) and PO_Super_Cat in ('cooking','LAUNDRY')


Thank You
CJ

 

[hmckillop]

First thing I notice is

select @strSQL = "SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc  FROM vw_IMITMIDX_GroupingFields  as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat  Where VW.Prod_Cat < '990'   and Commodity_Cd  in ( '"+@a+ "' ) and  PO_Super_Cat  in ('" +@b+ "')"

should be something like

select @strSQL = 'SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc  
FROM vw_IMITMIDX_GroupingFields  as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat  
Where VW.Prod_Cat < 990   and Commodity_Cd  in (' + @a +  ') and  PO_Super_Cat  in (' + @b + ')'

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

sorry should be

select @strSQL = 'SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc  
FROM vw_IMITMIDX_GroupingFields  as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat  
Where VW.Prod_Cat < 990   and Commodity_Cd  in (''' + @a +  ''') and  PO_Super_Cat  in (''' + @b + ''')'

note the single quotes only.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[CJwater]

Thank you both for your prompt responses but neither solves the problem... The error line number is on the execute @strSQL line. I can run all 3 printed SQL versions of the code which is the first line that print print (@strSQL). Is there something wrong with how I'm executing Line 24 (execute (@strSQL))?

1)
SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc FROM vw_IMITMIDX_GroupingFields as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat Where VW.Prod_Cat < 990 and Commodity_Cd in (elx','frg) and PO_Super_Cat in (cooking','LAUNDRY)

2)
Server: Msg 203, Level 16, State 2, Procedure Web_GetVendorCat, Line 24


3) Same error for all 3 queries
The name 'SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc FROM vw_IMITMIDX_GroupingFields as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat Where VW.Prod_Cat < 990 and Commodity_Cd in (elx','frg) and PO_Super_Cat in (cooking','LAUNDRY)' is not a valid identifier.

CJ

 

[jbenson001]

Look at the generated SQL from your print statements. Your quotes are not matched correctly in the IN statement.

 

[markros]

Using the code from

http://blogs.lessthandot.com/index..../the-ten-most-asked-sql-server-questions--1#3

you can create a table-valued function to split comma-delimited string into a table, then you would just join with this function to get your result - no need to execute dynamic SQL.

 

[CJwater]

Sorry about that, that was the result of the first answer and it did return a sql error upon execution in query analyzer.My code (and the second answer) both return the following and both work in query analyzer.
SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc FROM vw_IMITMIDX_GroupingFields as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat Where VW.Prod_Cat < 990 and Commodity_Cd in ( 'elx','frg' ) and PO_Super_Cat in ('cooking','LAUNDRY')
Server: Msg 203, Level 16, State 2, Procedure Web_GetVendorCat, Line 23

The name 'SELECT DISTINCT VW.Prod_Cat, VW.Prod_Cat_Desc FROM vw_IMITMIDX_GroupingFields as VW LEFT JOIN tblProductSet PS ON VW.prod_cat = PS.Prod_Cat Where VW.Prod_Cat < 990 and Commodity_Cd in ( 'elx','frg' ) and PO_Super_Cat in ('cooking','LAUNDRY')' is not a valid identifier.

But query analyzer is showing a ' before the query and another after.

Any other thoughts?
Thanks Again
CJ

 

[hmckillop]

stupid me, replace
execute @strSQL

with

exec (@strSQL)

this should work.

seen this a million times before, should have realised

"I'm living so far beyond my income that we may almost be said to be living apart

 

[ergen]

That might help :

http://www.jacek-szarapa.com/index.php?p=sql&d=2

just create table from comma delimited and join it the rest of the query... simple as that...

 

[CJwater]

hmckillop, "Thank you so much". Real simple, yet I wasted lots of time...

 

[CJwater]

Markros,
I looked at the split string example, but I'm not following how your number example would split a string of strings and add a single quote on each side of the string. If you have a few minutes could you explain it?

Thank You
CJ

 

[markros]

You don't need a single quote if your result would be a table with character field having your string. Check another implementation of the split function to see the output as a table.

http://www.berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split#comment-126

Once you have the table with strings you can use this select

select * from myTable inner join dbo.ufn_split(@Comma_Delimited_Categories_List,',') FN on myTable.Category = FN.Items