Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

CJwater

IS-IT--Management
Mar 26, 2008
34
US
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



 
First thing I notice is
Code:
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

Code:
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
 
sorry should be

Code:
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
 
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

 
Look at the generated SQL from your print statements. Your quotes are not matched correctly in the IN statement.
 
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

 
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
 
hmckillop, "Thank you so much". Real simple, yet I wasted lots of time...
 
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
 
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.


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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top