locked
Passing multiple values to a parameter in a stored procedure RRS feed

  • Question

  • Can anyone help, I have the following stored procedure



    create procedure TEST_procedure
    @exampleid
    as
    update tablea
    set text_field = 'Y'
    where
    example_id in (@exampleid)

    I would like to be able to pass multiple values to the @example id parameter so the procedure will update the relevant fields.

    I'm not sure how to do this without creating more parameters.
    Monday, November 11, 2013 7:49 PM

Answers

  • Hi,

    try this code it may help you

    create table tablea (id1 int, nom varchar(20)) 
    insert tablea values (1,'aa')
    insert tablea values (2,'bb')
    insert tablea values (3,'cc')
    go
    
    CREATE TYPE tableType AS TABLE
     (                     
          id1 int
    	   )
    	  
    
     GO
    
    create procedure TEST_procedure (@exampleid tableType READONLY) 
    as
    
     update tablea
     set nom = 'Y'
     where
     id1 in (select * from @exampleid)
    go
    declare @table tableType
    insert into @table values (1)
    insert into @table values (2)
    execute TEST_procedure @table ;

    Best regards.
    • Marked as answer by system243trd Wednesday, November 13, 2013 7:02 PM
    Monday, November 11, 2013 8:56 PM

All replies

  • Hi,

    try this code it may help you

    create table tablea (id1 int, nom varchar(20)) 
    insert tablea values (1,'aa')
    insert tablea values (2,'bb')
    insert tablea values (3,'cc')
    go
    
    CREATE TYPE tableType AS TABLE
     (                     
          id1 int
    	   )
    	  
    
     GO
    
    create procedure TEST_procedure (@exampleid tableType READONLY) 
    as
    
     update tablea
     set nom = 'Y'
     where
     id1 in (select * from @exampleid)
    go
    declare @table tableType
    insert into @table values (1)
    insert into @table values (2)
    execute TEST_procedure @table ;

    Best regards.
    • Marked as answer by system243trd Wednesday, November 13, 2013 7:02 PM
    Monday, November 11, 2013 8:56 PM
  • Please see below link

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6bb97a45-5837-48b5-ab63-4c3c69f64030/passing-multiple-values-in-parameter?forum=transactsql#02fec7ae-1be8-4ec1-bd6e-8dae33d67028


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, November 11, 2013 11:00 PM
  • Every few weeks a noob posts this question without doing any research. Better answer:
    http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/
    http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/

    Do this with a long parameter list. You can pass up to 2000+ parameters in T-SQL, which is more than you probably will ever need. The compiler will do all that error checking that the query version and the procedural code simply do not have unless you write a full parser with the standard error codes. You can now pass local variables to your procedure; you can pass other data types and get automatic conversions, etc. In short, this is just good software engineering. 

    CREATE PROCEDURE LongList
    (@p1 INTEGER = NULL,
     @p2 INTEGER = NULL,
     @p3 INTEGER = NULL,
     @p4 INTEGER = NULL,
     @p5 INTEGER = NULL)

      x IN (SELECT parm
              FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X(parm)
            WHERE parm IS NOT NULL;

    You get all the advantages of the real compiler and can do all kinds of things with the values. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, November 12, 2013 1:02 AM
  • You can use Dynamic sql for this, and then @exampleid can hold the values like

    '''A11'',''A12'',''A13'''


    Many Thanks & Best Regards, Hua Min

    Tuesday, November 12, 2013 2:03 AM
  • Using a table-valued parameter as Khaled showed is the way to go. In case you are using .Net, this article on my web site includes examples how to do this:
    http://www.sommarskog.se/arrays-in-sql-2008.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 12, 2013 7:15 AM
  • Do this with a long parameter list.

    No, don't. Of all possible solutions, this is the worst together with dynamic SQL.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 12, 2013 10:52 PM
  • You can use Dynamic sql <http://technet.microsoft.com/en-us/library/ms188001.aspx> for this, and then @exampleid can hold the values like

    '''A11'',''A12'',''A13'''

    You can. But you shouldn't. Most emphatically shouldn't. This is 2013, not 1996.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 12, 2013 10:53 PM
  • Thanks khaled and Erland, much appreciated.
    Wednesday, November 13, 2013 7:04 PM