Another solution for doing this is to batch your state IDs up and passing
them to the stored proc in one call. You can do this by changing your data
type from INT (or real) to VARCHAR(1000) -- or some size other than 1000
that would be large enough to handle all your possible selections. Then, in
your stored proc you can do something like...
CREATE procedure sp_Blah
@SomeIntParam INT,
@InputParam VARCHAR(1000)
AS
DECLARE @sSQL VARCHAR(4000)
SET @sSQL = '
DELETE FROM tblYourTable
WHERE SomeIntField = ' + cast(@SomeIntParam as varchar) + '
and StateField in (' + @InputParam + ')
'
EXEC(@sSQL)
GO
--- I added another INT field to the stored proc param list to show you that
you'll need to cast all your variables to varchar when building this query
-- but since @InputParam already is -- it's good to go w/out a cast. This
is how we handle all situations where we need to pass
multiple params in -
but only want to execute one call. Only caveat is that you may exceed your
variable size limits -- for example @sSQL will truncate if your input params
build the
string over 4000 characters - so you may want to do some input
validation in the proc - or before you call it.