Stored Procedures with a list and the Parameter?

[philosinger]

Background I am executing this from an application where the user selects the states that they want to have moved.
Problem/Question
Is is possible to execute a stored procedure with a list as the parameter. Here is the storedProcedure I am trying to run

CREATE PROCEDURE [sp_mysp] @StateAbbr varchar(255) AS
select State, sum(col1) as col1, sum(col2) as col2
into NewTable
from OldTable
where State in (@StateAbbr)
group by State
order by State

I try to execute:
sp_mysp 'VA', 'MD', 'CA'
however I get an error stating that I am providing to many parameters.

What should my data type be? Can I even do this?

 

[wushutwist]

Can't do this (as far as I know).
Loop through your array and call the stored procedure for each value.

Wushutwist

 

[TomSark]

You are trying to pass 3 arguments to a proc defined to accept only 1 argument. If you have a variable number of agrs, put them all in a string and parse it inside the proc (you can pass an array), or put them in a table and process them from the table in your proc. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TiggerMom]

You bet there is a way.
You need to create a local variable of string type
and then assign a concatenated select statement to it
then execute the string. Here is an example for you:

CREATE PROCEDURE spStoredProcName (@ParameterStr varchar(500))
AS
Declare @sql varchar(8000)

set @sql='SELECT column1, column2, column3
FROM table1
WHERE ( (column1 IS NULL) AND
column2 IN ('+@ParameterStr+')'
exec (@sql)

Note: if your parameter is a date, you must put
the date parameter in triple single quotes when
using it in your select statement string; i.e.,
'''+@Date+'''

Hope this helps you out!
TiggerMom

 

[jagilman]

TriggerMom, it would appear that you could create a basic sql statement that only required the user to generate the "where part of the statement" then put the whole thing togeather and execute it.

Do I have part of this right?
John A. Gilman
gms@uslink.net