Multi value parm in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multi value parm in SQL

Author

Topic

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-11-21 : 15:21:47

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 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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-21 : 15:25:43

there ae several ways, but I suggest thay you find/make a split function and populate a table variable with all the CustomerNo's and then join to it.

This may help:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
or this
http://www.sommarskog.se/arrays-in-sql-2005.html

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 00:32:30

quote:
Originally posted by latingntlman

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 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 ','+@CustNo + ',' LIKE '%,'+CAST(c.CustomerID AS varchar(10)) + ',%'
ORDER BY
c.CompanyName,
o.OrderDate

modify like above. Also i belive it should be AND and not OR as it has satisfy both date condition and numbers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources