Tech Off Thread

9 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

How can I send ARRAY to SP in SQLSERVER 2005

Back to Forum: Tech Off
  • User profile image
    JuNK64

    I have 2 related tables I code a SP that inserts the master table and a trigger on it when inserting master table that calls second SP
    but ı must insert detail table multiple rows.So ı want to send ARRAY into secondary SP How can I do that ? Or is it possible ? If yes can anybody send me a code snippet ? Thanks

  • User profile image
    peng

    I'm sure someone else will tell you the correct way to do this, or at least, a more sensible way but when I have needed to do it in the past I have sent the array in as a delimited string e.g. 1|2|3|4 and then split it into a temp table using a custom function call in the stored proc. I'm sure this performs terribly but it wasn't so important in the app that I used it in and it worked.

    I can post the split function if you need it but as I say I am sure that there will be a better way with a custom data type or something that I am unaware of...

    Good luck...


  • User profile image
    Another_​Darren
  • User profile image
    Matthew van Eerde

    There's a couple of ugly ways that I've used before.

    Let's put a concrete face on the problem.  Suppose you have a Products table and a Carts table.  You have a page where a user can check boxes of various products to add them to their cart.  You want to call a single stored procedure with a list of the product IDs that were checked, and have them all added to the users cart.

    Your SP prototype looks like this:

    EXECUTE AddProducts(
       @UserID int,
       @ProductIDs nvarchar(512) -- comma delimited: N'5, 13, 12'
    )

    The stored procedure is supposed to insert rows into the Carts table.  The Carts table has two important columns: ProductID and UserID.  The UserID for each new row should be the passed @UserID; each of the passed @ProductIDs should be represented in one new row.

    FIRST METHOD: use LIKE
    INSERT INTO
       Carts (UserID, ProductID)
    SELECT
       @UserID,
       ProductID
    FROM
       Products
    WHERE
       N', ' + @ProductIDs + N', ' LIKE N'%, ' + CAST(ProductID AS NVARCHAR(20)) + N', %'

    One disadvantage of this method is it won't be able to make use of the index on ProductID.  So it will be slow, if the Products table is large.

    If the table is large, and the index is there, I revert to the following method:

    SECOND METHOD: use sp_executesql

    -- check for SQL injection attacks
    IF @ProductIDs LIKE N'%[^0-9, ]%'
    BEGIN
       RAISERROR(...) -- raise an appropriate error
       RETURN (1) -- under no circumstances continue
    END

    DECLARE @SQL nvarchar(4000)
    SELECT @SQL =
    N'INSERT INTO Carts(UserID, ProductID)
    SELECT
       ' + CAST(@UserID AS NVARCHAR(20)) + ',
       ProductID
    FROM
       Products
    WHERE
       ProductID IN (' + @ProductIDs + ')'
    '

    EXECUTE sp_executesql @SQL

  • User profile image
    JuNK64

    I think I HAVE TO USE XML and IT will be better.But ITS SO COMPLICETED Sad

     

     

  • User profile image
    thepuffin

    You're definitely approaching the problem in the wrong manner if you think you need to use an array in SQL. Perhaps you can explain exactly what it is you need to do and we'll be able to suggest a better approach.

  • User profile image
    figuerres

    Yeah I read the first post and I am not sure what you are doing...

    some thing about a trigger during an insert ??

    you do know about the virtual inserted table??

    that an insert trigger can fire for one row or n rows to insert ??

    perhaps a clear example of what you need to do might lead to a simple way to solve it.

  • User profile image
    phreaks

    JuNK64 wrote:
    

    I think I HAVE TO USE XML and IT will be better.But ITS SO COMPLICETED Sad



    It really isn't complicated at all.
    You just need to get comfortable with it.

    Using Edge Tables with XMl in SQL Server is efficient and also very fast.

    CREATE Procedure spc_myXMLProc(@Xml nText)

    AS

    CREATE TABLE #myTable (
      tempID INT IDENTITY(1,1) NOT FOR REPLICATION,
      SomeBit bit,
      SomeAmount float,
      SomeDate varchar(50)
      )

    -- Prepare XML using SQL's XML Preprocessor 
    -- In this Case the namespace is from a DataSet
     EXEC sp_xml_preparedocument @iDoc OUTPUT, @Xml, '<MyDataSet xmlns:fn="http://tempuri.org/MyDataSet.xsd"/>'

    INSERT INTO #myTable (
       SomeBit,
       SomeAmount,
       SomeDate
    ) SELECT
       Case SomeBit
          WHEN 'True' Then 1
          ELSE 0
       END,
       Cast(SomeAmount as Float),
       SomeDate
    FROM
       OPENXML(@iDoc, '//fn:MyDataSet', 3)
    WITH
       (
          SomeBit varchar(5) 'fn:SomeBit', -- SomeBit is a Node in the XML
          SomeAmount varchar(50)  'fn:Amount',
          SomeDate varchar(50)  'fn:MyDate'
       )
       
    -- Free Preprocessor memory
     EXEC sp_xml_removedocument @iDoc

    SELECT * FROM #myTable

  • User profile image
    Shawn C

    Well, you can send an "array" as a delimeted list into your stored procedure.  Then, convert the array into a temporary table which you can then navigate through.  I find this useful for sending "arrays" of checkbox values from a web form into a stored procedure.

    Function to convert list into temp table:

    CREATE FUNCTION dbo.iter_charlist_to_table
                        (@list      ntext,
                         @delimiter nchar(1) = N',')
             RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                                 str     varchar(4000),
                                 nstr    nvarchar(2000)) AS

       BEGIN
          DECLARE @pos      int,
                  @textpos  int,
                  @chunklen smallint,
                  @tmpstr   nvarchar(4000),
                  @leftover nvarchar(4000),
                  @tmpval   nvarchar(4000)

          SET @textpos = 1
          SET @leftover = ''
          WHILE @textpos <= datalength(@list) / 2
          BEGIN
             SET @chunklen = 4000 - datalength(@leftover) / 2
             SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
             SET @textpos = @textpos + @chunklen

             SET @pos = charindex(@delimiter, @tmpstr)

             WHILE @pos > 0
             BEGIN
                SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
                INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
                SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
                SET @pos = charindex(@delimiter, @tmpstr)
             END

             SET @leftover = @tmpstr
          END

          INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
       RETURN
       END

    USAGE Example:

      DECLARE curDept CURSOR FOR
     
      SELECT nstr FROM iter_charlist_to_table(@in_accounting_department_id, ',')

    *The function is not mine....not sure where I got it from...but it is really handy.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.