March 10, 2005 at 9:42 am
I have a table named Counties with the following:
State County
AR Washington
AR Union
TX Smith
TX Scott
TX Scully
OK Cherokee
I have written a stored procedure that I want to pull back the names for all of the state abbreviations I send in. There can be one or more states passed in. I can get it to work for one state but not for more than one. Here is my code:
DECLARE @stmt nvarchar(1000)
SET @stmt = 'SELECT DISTINCT county'
SET @stmt = @stmt + 'FROM dbo.counties '
SET @stmt = @stmt + 'WHERE abbr IN (@state) '
SET @stmt = @stmt + 'ORDER BY county'
EXEC sp_executesql @stmt, N'@state varchar(2000)', @prmState
@prmState is the input parameter of type varchar(500).
When I call the procedure with one state it works fine. The command I use is the following:
EXEC dbo.sp_get_counties 'TX'
When I try to call the procedure with more than one state, I either get errors or just get nothing back, depending on the formatting of the parameters. Ther following are some examples of what I have tried:
EXEC dbo.sp_get_counties 'AR,TX'
EXEC dbo.sp_get_counties '''AR'',''TX'''
EXEC dbo.sp_get_counties ''AR','TX''
This is my first attempt with sp_executesql. Can someone tell me what I am doing wrong?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
March 10, 2005 at 10:04 am
Check this
EXEC dbo.sp_get_counties '''AR','TX'''
My Blog:
March 10, 2005 at 11:33 am
See "Arrays and Lists in SQL Server" by Erland Sommarskog (SQL Server MVP) at http://www.sommarskog.se/arrays-in-sql.html
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply