October 8, 2009 at 12:37 pm
I have a stored procedure that looks like this. (I know its a syntax issue, but I can't pinpoint it)
I'll just show the query portion of if for now:
select agent, sales from tblAgent
where state in (' + @state + ')
When I run the stored procedure, @state looks like this
'DE,CT,NJ,NY' instead of 'DE','CT','NJ','NY'
and its not returning me any data for the states, how can I make the IN clause so it returns me all the states passed into the procedure?
I've tried ('" + @state + '") and got a syntax error. Am I missing something?
October 8, 2009 at 1:04 pm
It's a long read, but this looks to be exactly what you're after:
http://www.sommarskog.se/arrays-in-sql-2005.html
- Jeff
October 9, 2009 at 7:09 am
That is a good read. Basically SQL Server sees your @state variable as a single string not a delimited list for use with IN. You need to split the string variable, putting the values in table form and then join to that table. There are many scripts out there, even on this site that will do that. Here's another good read about this, http://www.sqlservercentral.com/articles/T-SQL/63003/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply