passing multiple values for one parameter

Question

Post reply

passing multiple values for one parameter

SQL_NuB

Hall of Fame

Points: 3666
More actions
November 24, 2008 at 7:22 am

#203299

I have a stored procedure such as this;
select name, center, region, customer
from tbl1 where center = @center and state =@state
My question is, on the web form, State can be passed as a single state id (1) or such as
state = 1,2,3,4,5,6,7, and so on.
Within the stored procedure, how can I accomodate the ( , ) in the string and handle it on the DB side instead of having the developer calling the database for each state being passed in?
so the query could look like:
select name, center, region, customer
from tbl1 where center = @center and state = 1
or
select name, center, region, customer
from tbl1 where center = @center and state = 1,2,3,4,5,6,7,8,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

I use xml in order to aviod having to use dynamic sql. This should explain how you would use both.

http://www.mssqltips.com/tip.asp?tip=1568

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 2

I got that to work within my normal query, how would this work in a pivot query?

Russell.Taylor-933868 SSC Enthusiast Points: 191 More actions · Answer 3

Hi,

I am a fairly new DBA but what about this:

DECLARE @sqlStmt varchar(8000)

SELECT @sqlStmt = 'select name, center, region, customer

from tbl1 where center = ' + @center + ' and state IN (' + @state + ')'

EXEC (@sqlStmt)

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 4

I have that working, I'm trying to get it to work within my query that is doing a pivot.

here is my pivot query:

SELECT *

FROM (

'SELECT tblCustomer.CusterName, tblCustomer.Date as TotalSales, tblMAIN.Region

FROM tblCustomer INNER JOIN

tblOrders ON tblCustomer.CustID = tblOrders.CustID

where

tblOrders.State IN (' + @State + ') and

tblOrders.[Date]

BETWEEN

COALESCE(NULLIF(@StartDate,''),tblOrders .[Date])

AND

COALESCE(NULLIF(@EndDate,''),tblOrders .[Date])'

) AS D

PIVOT

(

Count(TotalSales)

For Region

IN ([NE], [SE], [SW], [NW])

)

AS P

Russell.Taylor-933868 SSC Enthusiast Points: 191 More actions · Answer 5

Without having the tables etc to test this against this should be pretty much there:

This would encapsulate all the code into the variable then run it as a complete statement instead of trying to nest the select.

Another alterntaive woulb be to have the nested part as a function and join the results to the function.

DECLARE @sqlstmt varchar(8000)

'SELECT *