Apr
21
Written by:
Steve Gray
4/21/2010 4:15 PM
In SSRS, there is a limitation that you cannot pass a multivalue parameter to a stored procedure. I read a couple of ways to get around that, the best being this one from Mark Vaillancourt. The ‘Scooby Do’ references are pretty funny. Ruh roh.
http://markvsql.com/index.php/2010/01/jinkies-passing-multivalue-ssrs-parameters-to-stored-procedures
In the spirit of laziness, I wanted a slightly simpler answer. I prompt the use to enter a comma separated list of customers, and parse them into a table in SQL. Then, I join that table to the customer master table in my application. Or you could do a ‘WHERE CUSTNMBR IN (SELECT CUSTNMBR FROM @MYTABLE)’
Code Snippet
- declare @customerNumber varchar(100)
- declare @customers table (custnmbr varchar(15))
- declare @customer varchar(15)
- declare @pos int
-
- set @customerNumber = '1234,qwer,zxv'
- set @pos = CHARINDEX(',',@CustomerNumber)
-
- while @pos > 0 begin
- set @customer = LEFT(@customernumber,@pos-1)
- set @customer = RTRIM(ltrim(@customer))
- insert into @customers(custnmbr) values (@customer)
- set @customerNumber = SUBSTRING(@customerNumber,@pos + 1 , 100)
- set @pos = CHARINDEX(',',@CustomerNumber)
- end
-
- set @customer = RTRIM(ltrim(@customernumber))
-
- insert into @customers(custnmbr) values (@customer)
- select custnmbr from @customers
As always, I welcome your comments!