none
IN Operator Parameter using stored procedure RRS feed

  • Question

  • Hi Guys,
    I'm running a reporting services report using stored procedure and I'm facing a dilemma with the IN Operator in the stored procedure.

    1. Can someone please help me out debugging the statement below? (run it on AdventureWorks Database)
    2. Can I have the @Title Parameter based on another select statement something like this 
    Set @Title = SELECT Title FROM TableTitle

     

    DECLARE @Title varchar(1000)
    
    set @Title  = 'Design Engineer', 'Tool Designer', 'Marketing Assistant'
    
    SELECT FirstName, LastName, e.Title
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c 
    ON e.ContactID = c.ContactID
    WHERE e.Title IN (@Title)
    

     

    Appreciate all the help on this.

    Thank you



     


    John
    Wednesday, April 15, 2009 11:17 PM

Answers

  • Jhon,

    If the Title is comma separated parameter without single quotes for each value then you can use the below code

    DECLARE @Title varchar(1000)
    declare @titles table(Title nvarchar(300))
    
    set @Title  = 'Design Engineer,Tool Designer,Marketing Assistant'
    
    insert into @titles
    select DISTINCT Title 
    from HumanResources.Employee 
    WHERE ','+ @Title +',' like '%,'+ Title +',%'
    
    
    SELECT FirstName, LastName, e.Title
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c 
    ON e.ContactID = c.ContactID
    join @titles e1
    on e1.title = e.Title
    

    Chandra, http://www.ggktech.com
    • Marked as answer by Codernater Friday, April 17, 2009 5:22 PM
    Friday, April 17, 2009 11:18 AM
  • Thanks Chandra,
    your approach actually worked but it seems complicated to me to embed this new join into my final query because the final query is joining 5 tables and it's very long!!!! (20K Words!!! not sure how many characters length!!)

    I had to use a different route to include the IN parameter in my reporting services.
    I created DataSet2 that include All the Title, then I took out the IN parameter from my stored procedure (Stored procedure is running for all Titles)

    On the reporting services Report Parameters I added a new parameter called Title(dataset2, MultiValue , from query value field is Title).
    Then from the Table Properties >>> Filters Tab I added under the Filter List:
            Expression Field =Fields!Title.Value
            Operator Field = IN
            Value Field = Parameters!Title.Value

    The disadvantage of this, the query is running for all Titles before it renders back in reporting services. In other words, it's slower than having the parameter filter the data on server side.

    Thank you all for your help


    John
    • Marked as answer by Codernater Friday, April 17, 2009 5:22 PM
    Friday, April 17, 2009 3:49 PM

All replies

  • Hi John,

    I think you are creating on multiple parameters then this should work for you

    Step 1:

    Add this query to dataset1

    DECLARE @Query Varchar(Max),@Title varchar(1000)
    SET @Title = Char(39) + Replace(@Title,',',Char(39)+','+Char(39)) + Char(39)

    SET @Query = 'SELECT FirstName, LastName, e.Title
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c
    ON e.ContactID = c.ContactID
    WHERE e.Title IN ('' + @Title+ '')'

    PRINT ' QUERY = '+ ISNULL(@Query,'NULL')
    Execute sp_executesql @Query

    Step 2:

    Add this query to dataset2

    Select Distinct Title From HumanResources.Employee
                    Order By Title

    Step 3:

    Open Report Parameter Properties
    Select Allow Multiple values checkbox
    Select Get values from a query
    Select dataset2 in Dataset dropdown and select Title as value field
    click on ok


    I hope it night be helpful for you


    Rajesh Jonnalagadda http://www.ggktech.com
    Thursday, April 16, 2009 6:12 AM
    Answerer
  • Thank you Rajesh for your quick response.

    I did exactly what you mentioned.
    I had to change the Parameter DataType to nvarchar instead of Varchar because sp_executesql was giving me an error (see below)
                       "Procedure sp_executesql, Line 1 Procedure expects parameter '@Statement' of type 'ntext/nchar/nvarchar'"


    After changing the datatype, I didn't get any records back, I applied your scenario to Reporting Services (followed your instructions), reporting services was prompting for the @Query Parameter!!!!

    Went back to the stored procedure and I modified it, took out the @Query parameter (see code below). I ran it again using reporting services, it ran okay but didn't show any records!!! eventhough I selected all records in the parameter @Title from the DataSet2, in addition I tried to run it based on one parameter on the dataset1 parameter value which is Tool Designer didn't get any records back!!!

    Just so you know, this is not my first time I use the IN Operator in my reporting services, most of the time I use it in a direct query in my reporting services reports, never used stored procedure before.
    However the query that eventually I'm trying to run is enormous and I believe that Reporting Services Query Designer has limitation to the Transact SQL Characters length (I was getting an error not enough memory) therefore I had to take Stored procedure route.

    Please let me know if you want me to clarify more on this issue.

    Thanks again for all your help.

    ALTER PROCEDURE [dbo].[spSelect_Title] 
    	-- Add the parameters for the stored procedure here
    	--@Query NVarchar(Max),
    	@Title NVarchar(MAX) 
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	SET @Title = Char(39) + Replace(@Title,',',Char(39)+','+Char(39)) + Char(39)
    
    	SELECT FirstName, LastName, e.Title
    	FROM HumanResources.Employee AS e
    	JOIN Person.Contact AS c 
    	ON e.ContactID = c.ContactID
    	WHERE e.Title IN ('' + @Title+ '')
    
    --	PRINT ' QUERY = '+ ISNULL(@Query,'NULL') 
    --	Execute sp_executesql @Query
    
    END
    



     

     

     


    John
    Thursday, April 16, 2009 2:37 PM
  • Hi John,

     

    For your problem, we can use dynamical statement to solve the issue.

    Here are 2 options to be clarified:

             1. A multi-value parameter will return an array value.

             2. It is not able to pass an array in a stored procedure.

     

    To solve the issue, we can use the following steps:

    1.              Cconvert the array into string that delimited by “,” using the function “Join” in SQL Server Reporting Services.

    2.              Pass the string in the stored procedure.

    3.              Combine the parameter and the statement into one simply string.

    4.              Execute the simply string.

     

    Here are the detailed steps for your reference:

    1.       In Report Designer, create a new dataset as “SELECT Title FROM TableTitle”.

    2.       Create a new multi-value parameter, set the available value from the dataset above.

    3.       Set the query of the main dataset as :

    ="spSelect_Title "&Join(Parameters!para.Value, ",")

    4.       Change the stored procedure as:

    ALTER PROCEDURE [dbo].[spSelect_Title]

             -- Add the parameters for the stored procedure here

             --@Query NVarchar(Max),

             @Title NVarchar(MAX)

            

    AS

    BEGIN

             -- SET NOCOUNT ON added to prevent extra result sets from

             -- interfering with SELECT statements.

             SET NOCOUNT ON;

     

             DECLARE @Query Varchar(Max)

     

             SET @Query = 'SELECT FirstName, LastName, e.Title

             FROM HumanResources.Employee AS e

             JOIN Person.Contact AS c

             ON e.ContactID = c.ContactID

             WHERE e.Title IN (' + @Title+ ')'

     

    --       PRINT ' QUERY = '+ ISNULL(@Query,'NULL')

             Execute sp_executesql @Query

     

    END

     

    If you have any more questions, please feel free to ask.

     

    Thanks,

    Jin


    Jin Chen - MSFT
    Friday, April 17, 2009 10:30 AM
    Moderator
  • Hi,

    Try this,

    CREATE PROCEDURE spSelect_Title
    	( @Title NVarchar(MAX) )
    	
    AS
    BEGIN 
    
    SET NOCOUNT ON;
    	
    DECLARE @Query NVarchar(Max)
    SET @Title = Char(39) + Replace(@Title,',',Char(39)+','+Char(39)) + Char(39)
    
    SET @Query = 'SELECT FirstName, LastName, e.Title
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c 
    ON e.ContactID = c.ContactID
    WHERE e.Title IN ('' + @Title+ '')'
    
    PRINT ' QUERY = '+ ISNULL(@Query,'NULL') 
    Execute sp_executesql @Query
    
    END
    

    Rajesh Jonnalagadda http://www.ggktech.com
    Friday, April 17, 2009 10:55 AM
    Answerer
  • Jhon,

    If the Title is comma separated parameter without single quotes for each value then you can use the below code

    DECLARE @Title varchar(1000)
    declare @titles table(Title nvarchar(300))
    
    set @Title  = 'Design Engineer,Tool Designer,Marketing Assistant'
    
    insert into @titles
    select DISTINCT Title 
    from HumanResources.Employee 
    WHERE ','+ @Title +',' like '%,'+ Title +',%'
    
    
    SELECT FirstName, LastName, e.Title
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c 
    ON e.ContactID = c.ContactID
    join @titles e1
    on e1.title = e.Title
    

    Chandra, http://www.ggktech.com
    • Marked as answer by Codernater Friday, April 17, 2009 5:22 PM
    Friday, April 17, 2009 11:18 AM
  • Thanks Rajesh, but it's still not working not showing any records. :-( 
    John
    Friday, April 17, 2009 3:47 PM
  • Thanks Chandra,
    your approach actually worked but it seems complicated to me to embed this new join into my final query because the final query is joining 5 tables and it's very long!!!! (20K Words!!! not sure how many characters length!!)

    I had to use a different route to include the IN parameter in my reporting services.
    I created DataSet2 that include All the Title, then I took out the IN parameter from my stored procedure (Stored procedure is running for all Titles)

    On the reporting services Report Parameters I added a new parameter called Title(dataset2, MultiValue , from query value field is Title).
    Then from the Table Properties >>> Filters Tab I added under the Filter List:
            Expression Field =Fields!Title.Value
            Operator Field = IN
            Value Field = Parameters!Title.Value

    The disadvantage of this, the query is running for all Titles before it renders back in reporting services. In other words, it's slower than having the parameter filter the data on server side.

    Thank you all for your help


    John
    • Marked as answer by Codernater Friday, April 17, 2009 5:22 PM
    Friday, April 17, 2009 3:49 PM

  • Hi All,

    My requirement is same I have a query of Type in condition query i.e

    I have table emp 

    Select * from emp where deptsupport in (?);

    ? The parameter will enter by user like GM, PG, AM or GM or GM,PG

    In my output, it will show one text field, then the user will end the values, please let me know how to 

    Proceed...

    Thanks in Advance
    • Proposed as answer by Suman007 Monday, March 24, 2014 6:53 AM
    • Unproposed as answer by Suman007 Monday, March 24, 2014 6:54 AM
    • Proposed as answer by Suman007 Monday, March 24, 2014 6:55 AM
    Monday, March 24, 2014 6:53 AM