none
How to pass multiple values as a parameter to a stored procedure?

    Question

  • Hello,

               I am currently working on a creation of SPs which can accept more than one value as a parameter and inside the sp it should be checked at the where condition, something like this,

    Create procedure dbo.sp_test
    @par nvarchar(max)
    as
    
    execute('select * from tablename where condition in '+'('+@par+')')
    
    
    end 
    go

    Something like this... 
    I have two questions, how to pass multiple value and other one which is most important is that the passing values are something like this,'[GBL GP406/CP2103 (Sterility by Membrane Filtration)]' 
    So how to keep '' in where condition since they are not int values?


    ANK HIT

    Thursday, May 24, 2012 1:11 PM

Answers

  • In SQL 2008 and up pass them as a table-valued parameter. For earlier versions pass them separated by the unique character that you know should never be in the field and then split by that character into a table and use that table.

    This article should explain everything about this problem:

    Arrays & Lists in SQL Server

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, May 24, 2012 1:19 PM
    Moderator

All replies

  • In SQL 2008 and up pass them as a table-valued parameter. For earlier versions pass them separated by the unique character that you know should never be in the field and then split by that character into a table and use that table.

    This article should explain everything about this problem:

    Arrays & Lists in SQL Server

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, May 24, 2012 1:19 PM
    Moderator
  • Right, it works but I did something like this,

    CREATE PROCEDURE get_product_names 
    @mylist as nvarchar_list_tbltype READONLY 
    AS
    begin
    
    if (select n from @mylist) is null
    begin
    select * from [LIMSDM].dbo.vw_BulkFill_Pivoted_temp
    --where ANALYTE in (select N from @mylist)
    order by ANALYTE
    end
    if (select n from @mylist) is not null
    begin
    select * from [LIMSDM].dbo.vw_BulkFill_Pivoted_temp
    where ANALYTE in (select N from @mylist)
    order by ANALYTE
    
    
    end
    end
    go

    It executed successfully,  now when i use below script to run,

    DECLARE @mylist nvarchar_list_tbltype
    INSERT @mylist(n) values ('AMD-MD-0003 (Final Conc. Human Insulin)(ng/mg Pro)'),('QCA-GN-0006 (Average A280 Conc)(mg/mL)')
    EXEC get_product_names @mylist
    

    it gives me errors like,

    (2 row(s) affected)
    Msg 512, Level 16, State 1, Procedure get_product_names, Line 6
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    Msg 512, Level 16, State 1, Procedure get_product_names, Line 12
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    


    ANK HIT




    • Edited by ank hit Thursday, May 24, 2012 2:15 PM
    Thursday, May 24, 2012 1:49 PM
  • I guess i got the solution of the problem, correct me if I am wrong, i modified something like this,

    alter PROCEDURE get_product_names 
    @mylist as nvarchar_list_tbltype READONLY 
    AS
    begin
    if (select COUNT(*) from @mylist)<>0
    begin
    select * from [LIMSDM].dbo.vw_BulkFill_Pivoted_temp
    where ANALYTE in (select N from @mylist)
    order by ANALYTE
    end
    if (select COUNT(*) from @mylist)=0
    begin
    select * from [LIMSDM].dbo.vw_BulkFill_Pivoted_temp
    order by ANALYTE
    end
    
    
    end
    go


    ANK HIT

    Thursday, May 24, 2012 2:20 PM
  • Hi,

    here is the problem

    if (select n from @mylist) is not null


    can you change this to

    if  exists(select n from @mylist) 

    or 

    if  (select count(n) from @mylist) >0

    Regards
    Satheesh


    Thursday, May 24, 2012 2:24 PM
  • there is still a problem, if i use create procedure get_product_names, it works but gives me an error if i use create procedure dbo.get_product_names 

    error is like,

    Msg 2715, Level 16, State 3, Procedure TEST_BULKFill, Line 1
    Column, parameter, or variable #1: Cannot find data type nvarchar_list_tbltype.
    Parameter or variable '@mylist' has an invalid data type.
    Msg 1087, Level 16, State 1, Procedure TEST_BULKFill, Line 5
    Must declare the table variable "@mylist".
    Msg 1087, Level 16, State 1, Procedure TEST_BULKFill, Line 10
    Must declare the table variable "@mylist".
    


    ANK HIT

    Thursday, May 24, 2012 2:38 PM
  • Hi,

    here is the problem

    if (select n from @mylist) is not null


    can you change this to

    if  exists(select n from @mylist) 

    or 

    if  (select count(n) from @mylist) >0

    Regards
    Satheesh


    Yes, I already changed that though. But i am getting an error for different issue, check below.

    ANK HIT

    Thursday, May 24, 2012 2:40 PM
  • hello,

    Can you provide the definition for the table type? Have you created nvarchar_list_tbltype with "dbo."

    Regards
    Satheesh


    Thursday, May 24, 2012 3:13 PM
  • In which schema did you create your table type? It should match the schema of the stored procedure.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, May 24, 2012 3:31 PM
    Moderator
  • Right because I have created it as nvarchar_list_tbltype and didn't use dbo. in it.

    But the problem is solved for now.

    Thank you guys.


    ANK HIT

    Thursday, May 24, 2012 4:41 PM