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

J SSCertifiable Points: 7822 More actions · Answer 1

J

SSCertifiable

Points: 7822

November 24, 2008 at 7:42 am

#902457

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

SQL_NuB

Hall of Fame

Points: 3666

November 24, 2008 at 7:57 am

#902464

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

Russell.Taylor-933868

SSC Enthusiast

Points: 191

November 24, 2008 at 7:59 am

#902467

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 *

FROM (

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