Recreating SSRS Data Source in TSQL for testing

Question

I have an SSRS data source that I would like to test out in SQL Server so that I can see the exact result set. The report that I'm using makes use of a multivalued parameter in the report which is passed into the query as a clause

AND T.OrderType IN (@OrderType)

What I would like to do is at the top of the statement in SQL to declare a variable to be referenced here. This is an example of an approach which I tried which did not work

DECLARE @OrderType VARCHAR(255); SET @OrderType = '''StringValue1'',''StringValue2''';

However, this failed to produce the desired results (No records were returned)

I can create the variable and have it successfully return rows for a single type, but generally I'd like to see the same behavior when testing the data source as I would in SSRS. The below is the code which successfully returns a single order type

DECLARE @OrderType VARCHAR(255); SET @OrderType = 'StringValue1';

Answer 1

You essentially want your WHERE clause to look like this: WHERE T.OrderType IN ('StringValue1','StringValue2',StringValue3'). This can be easily done if you convert the SSRS param to a comma-separated list and use a table variable.

First, in the dataset properties in the SSRS report, you can modify the parameter with an expression that transforms the value of the param into several strings separated by commas: =Join(Parameters!OrderType.Value,",").

To mimic this behavior in your test script: SET @OrderType = 'String1, String2, String3'.

Next, transform the contents of @OrderType into a table variable with a UDF that parses a comma separated list into a variable. You should be able to find many examples of this type of function on the web.

Once you have your table variable populated (we'll call it @tblOrderType), you can use the WHERE clause like this: T.OrderType IN (SELECT OrderType FROM @OrderType).

Answer 2

Create the function below.

CREATE FUNCTION [dbo].[Split] ( @InputString nvarchar(max), @Delimiter nvarchar(50) )

RETURNS @Items TABLE ( Item nvarchar(max) )

AS BEGIN IF @Delimiter = ' ' BEGIN SET @Delimiter = ',' SET @InputString = REPLACE(@InputString, ' ', @Delimiter) END

  IF (@Delimiter IS NULL OR @Delimiter = '')
        SET @Delimiter = ','


  DECLARE @Item                 nvarchar(max)
  DECLARE @ItemList       nvarchar(max)
  DECLARE @DelimIndex     INT

  SET @ItemList = @InputString
  SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
  WHILE (@DelimIndex != 0)
  BEGIN
        SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
        INSERT INTO @Items VALUES (@Item)

        SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+LEN(@Delimiter), LEN(@ItemList)-@DelimIndex)
        SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
  END -- End WHILE

  IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
  BEGIN
        SET @Item = @ItemList
        INSERT INTO @Items VALUES (@Item)
  END

  -- No delimiters were encountered in @InputString, so just return @InputString
  ELSE INSERT INTO @Items VALUES (@InputString)

  RETURN

END

use this select statement in the stored procedure

Select * from TableName where AND T.OrderType IN (select * from dbo.Split(@OrderType,','))

Answer 3

You can't do exactly what you are looking for, since T-SQL on its own won't expand a single parameter into an array of values inside an IN clause.

However, this article on Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters (by Erland Sommarskog) may get you close.