locked
how to manage sql procedure parameters in runtime RRS feed

  • Question

  • hi ;

    I want to create object collection parameters in sql procedure so like bellow or how ?

    CREATE PROCEDURE TEST object [] params arg as 

    SELECT FIELD FROM TABLE WHERE ID=arg[0]

    Thursday, January 3, 2019 10:26 PM

All replies

  • You should probably use a table-valued parameter. After all, you are in a database, so why not work with tabels?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 3, 2019 10:51 PM
  • Good day,

    Please check if this help you(you can see in the example how to pass multiple values using table-valued parameter to a stored procedure):

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017#Example


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Proposed as answer by Ashin_c Friday, January 11, 2019 9:44 AM
    Friday, January 4, 2019 12:08 AM
  • Hi Aly14,

     

    I think pituach has provided you a good link , you can refer to it . Also , I will provide a simple example for your requirement. Hope it can help you.

     

    /* Create a table type*/
    create type XTableType as table(ID int,Names varchar(10));
    go
      
    /* Creates a stored procedure with table value parameters as input*/
    create Procedure sp_test(@tp1 XTableType readonly)
    as
    set NoCount on
    select *,getdate()as date  from @tp1;
    set NoCount off
    go
    
      
    /* Declare table - valued parameter variables*/
    declare @tp2 as XTableType;
      
    /* Inserts data into a table-valued variable*/
    Insert into @tp2(ID,Names)
    select 1,'a'
    union select 2,'b'
    union select 3,'c';
      
    /* Passing variables to stored procedures*/
    EXEC sp_test @tp2;
    go
    /*
    ID          Names      date
    ----------- ---------- -----------------------
    1           a          2019-01-04 10:31:49.650
    2           b          2019-01-04 10:31:49.650
    3           c          2019-01-04 10:31:49.650
    */
    
    


     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Ashin_c Friday, January 11, 2019 9:44 AM
    Friday, January 4, 2019 2:38 AM