locked
Array as stored procedure parameter RRS feed

  • Question

  • How to handle Array parameter in sql server 2012. I want to pass a table with 150 records as parameter to a stored procedure. Can anyone help with an example please?

    Thursday, December 22, 2016 9:03 AM

Answers

  • Hi,

    Here is an example. I recommend creating a User defined Table type. This will help you in easy development.

    -- Create a User defined Table type
    CREATE TYPE UserDataType AS TABLE   
    ( 
    ID Int, 
    TextData varchar(255),
    DateValue datetime
    )
    GO
    
    --Create a Test stored Procedure
    
    Create proc p_TestProc
    (
    @UserDataType dbo.UserDataType ReadOnly
    )
    as
    --Select from the Table Type passed to SP
    Select * from @UserDataType
    
    GO
    
    -- Execute Stored Procedure and Pass Table 
    
    Declare @UserDataType dbo.UserDataType
    
    Insert into @UserDataType
    SELECT 1, 'SomeText', getdate()
    
    Exec p_TestProc @UserDataType

    Also go through the link : https://msdn.microsoft.com/en-us/library/bb510489%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396


    Kindly mark the reply as answer if they help

    • Marked as answer by Sagar_V Thursday, December 22, 2016 9:51 AM
    Thursday, December 22, 2016 9:13 AM

All replies

  • Please check if example give in below helps you.

    Table-Valued Parameters (Database Engine)


    Cheers
    Vaibhav
    MCSA (SQL Server 2012)

    Thursday, December 22, 2016 9:09 AM
  • Hi,

    Here is an example. I recommend creating a User defined Table type. This will help you in easy development.

    -- Create a User defined Table type
    CREATE TYPE UserDataType AS TABLE   
    ( 
    ID Int, 
    TextData varchar(255),
    DateValue datetime
    )
    GO
    
    --Create a Test stored Procedure
    
    Create proc p_TestProc
    (
    @UserDataType dbo.UserDataType ReadOnly
    )
    as
    --Select from the Table Type passed to SP
    Select * from @UserDataType
    
    GO
    
    -- Execute Stored Procedure and Pass Table 
    
    Declare @UserDataType dbo.UserDataType
    
    Insert into @UserDataType
    SELECT 1, 'SomeText', getdate()
    
    Exec p_TestProc @UserDataType

    Also go through the link : https://msdn.microsoft.com/en-us/library/bb510489%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396


    Kindly mark the reply as answer if they help

    • Marked as answer by Sagar_V Thursday, December 22, 2016 9:51 AM
    Thursday, December 22, 2016 9:13 AM
  • Thank you Vaibhav and Sunil.
    Thursday, December 22, 2016 9:51 AM
  • There are various ways by which you can pass array or multiple values as a single Parameter in a Stored Procedure or a Function:

    #1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP or UDF, check here.

    #2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP, check here.

    #3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any parameter with the SP, check here.

    #4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using user-defined table types. These TVPs can then be used to send multiple rows of data to SPs or UDFs, without creating a temp table or multiple parameters, check here.

    #5 – Passing a JSON string: as a NVARCHAR datatype parameter. We will need to parse the JSON inside the SP, check here.


    ~manoj


    Thursday, December 22, 2016 10:10 AM
  • How to handle Array parameter in sql server 2012. I want to pass a table with 150 records [sic: rows are nothing like records!]  as parameter to a stored procedure. Can anyone help with an example please?

    No, RDBMS does not have arrays; all of values are scalar and this is the basis of all the normal forms. You can use a table, a table valued parameter or a long parameter list. 

    https://www.simple-talk.com/sql/learn-sql-server/values-and-long-parameter-lists/
    https://www.simple-talk.com/sql/learn-sql-server/values-and-long-parameter-lists-part-ii/


    --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

    Thursday, December 22, 2016 7:17 PM