ssrs 多值参数存储过程

问题描述：

我在 SSRS (2008) 中创建了一份报告，该报告的数据来自 MS SQL 中的存储过程.

I have a report im creating in SSRS (2008) and the data for the report is coming from a stored procedure in MS SQL.

我想要一个多值参数(做了一百万次，但不是用 SP)

I want to have a multiple value parameter (done this a million time but not with a SP)

我根据查询创建了参数，勾选了允许多个值并修改了 SP 以使用 IN 语句(所以 IN (@Param))

I have created the parameter based on a query, ticked the allow multiple values and modified the SP to use the IN statement (so IN (@Param))

它只是不起作用 SQL Server 探查器显示这被传递

It just wont work SQL Server profiler show this being passed

@RC=N'1,2'

如果我选择一个值，它会起作用(返回一个值)

If I choose one value, it works (returning the one value)

我看过有关拆分加入的帖子，但我尝试了所有这些都无济于事.

I have seen posts about splitting joining and i've tried all these to no avail.

任何想法(当然它很简单！！)理想情况下，id 也希望能够返回 NULL(或者要求太多?

Any ideas (surely its simple!!) Ideally id like to be able to return NULL too (or is that too much to ask?

谢谢大家

答

在你的数据库中创建这个函数:

Create this function in your database:

USE [YourDatabase]
GO

/****** Object:  UserDefinedFunction [dbo].[SplitListToTable]    Script Date: 5/21/2018 8:13:29 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--==========================================================
-- Author: Gugg
-- Created: 2015-10-06
-- Description: Converts a delimited string into a table of values.  Based heavily on code from this url:
--              https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ead7ceb-3fdd-4625-aa82-1d4195f984b1/passing-multivalue-parameter-in-stored-procedure-ssrs
-- Modification History:
--===========================================================

CREATE FUNCTION [dbo].[SplitListToTable]
    (
      @List NVARCHAR(2000) ,
      @SplitOn NVARCHAR(5)
    )
RETURNS @RtnValue TABLE
    (
      Id INT IDENTITY(1, 1) ,
      Value NVARCHAR(100)
    )
AS
    BEGIN
        WHILE ( CHARINDEX(@SplitOn, @List) > 0 )
            BEGIN 
                INSERT  INTO @RtnValue
                        ( Value
                        )
                        SELECT  Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@SplitOn, @List) - 1))); 
                SET @List = SUBSTRING(@List, CHARINDEX(@SplitOn, @List) + LEN(@SplitOn), LEN(@List));
            END; 

        INSERT  INTO @RtnValue
                ( Value )
                SELECT  Value = LTRIM(RTRIM(@List));
        RETURN;
    END;



GO

然后你可以像这样解析你的参数:

Then you can parse your parameter like this:

SELECT *
FROM YourDatabase.dbo.YourTable
WHERE YourColumn IN(SELECT value FROM dbo.SplitListToTable(@YourParameter, ','))

ssrs 多值参数存储过程

相关推荐