September 12, 2015 at 11:44 pm
Sorry, maybe this is only tangentially related to SSRS. I know I can set a parameter to be multi-valued in SSRS and filter inside SSRS, but what if I want to pass that back to a stored procedure?
i'm sure you all know the drill... I have a stored procedure, like this:
CREATE PROC getMyRecords
@ProtocolNo VARCHAR(20)
AS
SELECT <field list>
FROM <table>
WHERE ProtocolNo = @ProtocolNo;
If I want to pass a multi-valued parameter, can I use DelimitedSplit8K to do it?
Something like
SELECT *
FROM <table>
CROSS APPLY (DelimitedSplit8K(@ParamList)
Apologies if I'm asking a dumb question... just point me at the article/post that explains how to do it. No extra words necessary.. I know I'm missing something really simple...
(Maybe I just have to read Jeff's article when I'm more awake?)
Thanks,
Pieter
September 13, 2015 at 12:30 am
Two quick suggestions, first uses the DelimitedSplit8K function for a simple delimited list of parameters and the second uses XML for more complex parameter structure.
😎
Simple parameter string
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.USP_TEST_MULTIVAL_STR_PARAM') IS NOT NULL DROP PROCEDURE dbo.USP_TEST_MULTIVAL_STR_PARAM;
GO
CREATE PROCEDURE dbo.USP_TEST_MULTIVAL_STR_PARAM
(
@PARAM_STR VARCHAR(8000)
)
AS
WITH PARAM_LIST AS
(
SELECT
XS.ItemNumber
,XS.Item
FROM dbo.DelimitedSplit8K(@PARAM_STR,CHAR(44)) AS XS
)
SELECT
PL.ItemNumber
,PL.Item
FROM PARAM_LIST PL
GO
DECLARE @PARAM_STR VARCHAR(8000) = '123,456,789,1011,12,13,14,15';
EXEC dbo.USP_TEST_MULTIVAL_STR_PARAM @PARAM_STR;
XML parameters
/* Safe place */
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.USP_TEST_MULTIVAL_XML_PARAM') IS NOT NULL DROP PROCEDURE dbo.USP_TEST_MULTIVAL_XML_PARAM;
GO
CREATE PROCEDURE dbo.USP_TEST_MULTIVAL_XML_PARAM
(
@PARAM_XML XML
)
AS
WITH PARAM_LIST(X,Y,Z) AS
(
SELECT
R.P.value('@X','INT') AS X
,R.P.value('@Y','INT') AS Y
,R.P.value('@Z','VARCHAR(10)') AS Z
FROM @PARAM_XML.nodes('R/P') AS R(P)
)
SELECT
PL.X
,PL.Y
,PL.X
FROM PARAM_LIST PL
;
GO
DECLARE @PARAM_XML XML = '<R>
<P X="0" Y="10" Z="A10" />
<P X="1" Y="11" Z="B11" />
<P X="2" Y="12" Z="C12" />
<P X="3" Y="13" Z="D13" />
<P X="4" Y="14" Z="E14" />
</R>';
EXEC dbo.USP_TEST_MULTIVAL_XML_PARAM @PARAM_XML;
September 13, 2015 at 1:01 pm
I've used Eirikur's first approach many times and found it to work great. I've used it for quite some time now to pass lists and it's very reliable.
December 7, 2016 at 12:15 am
Hi,
If you are receiving a multiple option parameter from SSRS it will be comma delimited. You can use this in dynamic SQL with an IN clause.
Cheers
Jamie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply