locked
Passing array to stored procedure RRS feed

  • Question

  • Can some one tell me the way to pass Table or Array or list of values to stored procedure?

    [Note:  I already know some ways which are not useful in my case:

    - By passing a list of values separting each with a delimeter like comma(,)

    - Using XML

    ]

     

    Any other way?

     

     

    Please, its urgent

    Thanks in advance

     

     

     

    Wednesday, September 27, 2006 11:36 AM

Answers

  • How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

    The stored procedure reads the values from the table where userId = @userId and deletes the values at the end to keep the table clean.

    Friday, September 29, 2006 2:45 AM
  • A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Friday, September 29, 2006 10:03 AM

All replies

  • Wednesday, September 27, 2006 1:59 PM
  • Thanks Jens...

    The reference sites you provided mentioned things I already tried...

    Please can you show me other ways to do it...

    Cause my project has bulky transactions.... So these methods won't work here....

    I need to pass Entire Table or Array from C# (Front end) to stored procedure...

    and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems)  for this task.

    I need to pass these data in other ways...

    Please help me out...

     

     

    Thursday, September 28, 2006 7:36 AM
  • How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

    The stored procedure reads the values from the table where userId = @userId and deletes the values at the end to keep the table clean.

    Friday, September 29, 2006 2:45 AM
  • A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Friday, September 29, 2006 10:03 AM
  • Thanks Jens and ,

     

    I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

    To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

    This table is dynamic, it can be created by passing DataTable from C#....

    It solved my problem....

    Thanks both of you for giving me idea....

     

     

    Friday, October 13, 2006 5:27 AM
  • Hi,

    Jens and Lakshmana

    The Idea you guys gave me helped me a lot.

    I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

    manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

    To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

    This helped me a lot (though it is a negative from performance point of view)...

    Thank you guys....

    Friday, October 13, 2006 5:52 AM
  • Hi All
    Tuesday, November 28, 2006 3:16 PM
  • Hi All

    thnx for ur comments

    really its all Usefull

    i Just Have small Problem .

    after i Created the  global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

    i Pass the temporary table Name to the Stored Procedure

    But i couldnt use it with aCursor or even Normal Select Statement.

    coz i have to concatinate it in the select Statemant

    Searchin For Help

    Thanks for all of u

    Tuesday, November 28, 2006 3:20 PM
  • Hi All.
    This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
    My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

    I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
    On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
    Here is the function:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        Lucian@Probia.co.za
    -- Create date: 2006/11/22
    -- Description:    Returns table for delimited string
    -- =============================================
    Create FUNCTION [dbo].[DelimStrToTable]
    (
        -- Add the parameters for the function here
        @delimStr varchar(8000),
        @delimchar char
    )
    RETURNS
    @StrValTable TABLE
    (
        -- Add the column definitions for the TABLE variable here
        StrVal varchar(1000)
         
    )
    AS
    BEGIN
        -- Fill the table variable with the rows for your result set
        declare @strlist varchar(8000), @pos int, @delim char, @lstr varchar(1000)
        set @strlist = @delimStr
        set @delim = @delimchar

    while ((len(@strlist) > 0) and (@strlist <> ''))
    begin
        set @pos = charindex(@delim, @strlist)
       
        if @pos > 0
        begin
            set @lstr = substring(@strlist, 1, @pos-1)
            set @strlist = ltrim(substring(@strlist,charindex(@delim, @strlist)+1, 8000))
        end
        else
        begin
            set @lstr = @strlist
            set @strlist = ''
        end
        Insert @StrValTable values (@lstr)
        --print @lstr
    end
       
        RETURN
    END


    and now I can do this:

    Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

    or
    Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
    on A.AuthorId = convert(int,IDLst.strval)

    One is of course limited by the size of  stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

    Lucian



    Tuesday, December 5, 2006 8:25 AM
  • I think the line
    StrVal varchar(1000)
    can be changed to
    StrVal varchar(10)
    The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.
    Tuesday, December 5, 2006 8:31 AM
  • Using table types?

    use [Northwind]

    CREATE TYPE [CustomerEmployee] AS TABLE(
     [CustomerID] [nchar](5) NULL,
     [EmployeeID] [int] NULL
    )
    GO

    CREATE PROCEDURE [ShipOrdersOnBatch]
     @customerEmployee CustomerEmployee READONLY
    AS
    BEGIN
     SET NOCOUNT ON;
     MERGE dbo.[Orders] AS Target
     USING (SELECT [CustomerID], [EmployeeID] from @customerEmployee) As Source ([CustomerID], [EmployeeID])
      ON Target.[CustomerID] = Source.[CustomerID] AND Target.[EmployeeID] = Source.[EmployeeID]
     WHEN MATCHED THEN
      UPDATE Set Target.[ShippedDate] = getutcdate();
    END
    GO

    DECLARE @MyTable [CustomerEmployee];
    INSERT INTO @MyTable VALUES ('VINET', 5);
    EXEC [ShipOrdersOnBatch] @MyTable


    Friday, October 26, 2012 10:36 PM