Multi Value parm in SPROC

Question

Multi Value parm in SPROC

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
November 21, 2008 at 1:24 pm

#132228

I'm passing three parameters in a sproc to a SSRS 2005 report. Two datetime fields (@FromDate, @ToDate) and an integer field (@CustNo). The intent is for a user to have the option of running the report by selecting a date range or by entery one or more Customer#'s. My challenge is for the @CustNo to be accepted in the report in a way that the user can enter one or more than one CustNo if they need to. i.e. Enter CustomerNo: 123456, 153222, 943382
Below is my code. I'm wondering if there's something that I need to do at the report side.
CREATE PROCEDURE prCustomerOrders
@startDate DATETIME,
@endDate DATETIME,
@CustNo INT
AS
SELECT
c.CustomerID,
c.CompanyName,
c.City,
c.ContactName,
o.OrderID,
o.OrderDate,
od.UnitPrice,
od.Quantity,
od.UnitPrice * od.Quantity AS ExtendedPrice
FROM
Customers c
INNER JOIN
Orders o ON c.CustomerID = o.CustomerID
INNER JOIN
OrderDetails od ON o.OrderID = od.OrderID
WHERE
o.OrderDate BETWEEN @startDate AND @endDate
Or c.CustomerID in (@CustNo)
ORDER BY
c.CompanyName,
o.OrderDate

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you can't substitute a variable for an object name like that directly...you need to use dynamic sql to accomplish what you want:

declare @sql varchar(max)

SET @sql ='SELECT

c.CustomerID,

c.CompanyName,

c.City,

c.ContactName,

o.OrderID,

o.OrderDate,

od.UnitPrice,

od.Quantity,

od.UnitPrice * od.Quantity AS ExtendedPrice

FROM

Customers c

INNER JOIN

Orders o ON c.CustomerID = o.CustomerID

INNER JOIN

OrderDetails od ON o.OrderID = od.OrderID

WHERE

o.OrderDate BETWEEN ''' + @startDate + ''' AND ''' + @endDate

+ ''' Or c.CustomerID in (' + @CustNo + ')

ORDER BY

c.CompanyName,

o.OrderDate '

Print @sql

exec (@sql)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 2

There is a trick to avoid the dynamic sql by using xml.

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