Tuesday 1 May 2012

How to TABLE or TABLE variable to SP



I want to pass a TABLE or TABLE variable to my stored procedure?  Can I do it? NO, SQL server does not support to pass a TABLE or TABLE variable as parameters of stored procedure. So how can I do it?

In this article I am trying to demonstrate the above scenario by using XML as the format to pass a table to a stored procedure.

We are going to transform the table or query result to an XML variable and pass to the stored procedure. We can either convert the XML parameters back to the TABLE variable or directly use the XQuery on the XML variable.

Let’s table a simple example to understand it.

“I have 2 table objects. One is Inventory table and it contains the item code and stock quantity. Another is Order details. When the order came in order details and when we run the stored procedure it always decrease the stock from inventory table by item wise.”
Please note that SQL Server 2008 is used for this example.


No first we create the Table definition

n  Inventory Table

CREATE TABLE [dbo].[tran_Inventory]
    (ITEMCD VARCHAR(20) NOT NULL PRIMARY KEY,
     STOCK   INT         NULL)
GO

INSERT INTO [dbo].[tran_Inventory]
            (ITEMCD, STOCK)
VALUES('A001', 1000), ('A002', 1000), ('A003', 1000)

GO

n  Order Table

CREATE TABLE [dbo].[Ord_Details]
      (
        ORDID    INT IDENTITY(1,1) NOT NULL,
        ITEMCD   VARCHAR(20)       NOT NULL,
        QTY      INT               NULL
      )

GO

INSERT INTO [dbo].[Ord_Details]
            (ITEMCD, QTY)
VALUES      ('A001', 20), ('A002', 30), ('A003', 40)

GO

Now Create the Stored procedure

CREATE PROCEDURE [dbo].[my_Proc]
             (
                   @param_x XML
             )
 AS
    SET NOCOUNT ON

    UPDATE tran_Inventory SET
           stock = stock - v.Qty
    FROM   tran_Inventory inv
           INNER JOIN (SELECT x.item.value('@ITEMCD[1]', 'VARCHAR(20)') AS ItemNumber,
                              x.item.value('@QTY[1]', 'INT') AS Qty
                       FROM   @param_x.nodes('//items/item') AS x(item)
                      ) v ON (v.ItemNumber = inv.ITEMCD)
   
    RETURN


Execution the Stored Procedure

DECLARE @x XML
SET @x = (SELECT ITEMCD, QTY FROM Ord_Details FOR XML RAW('item'), ROOT('items'), TYPE)
EXECUTE my_Proc @x

Observation the Result

SELECT * FROM tran_Inventory

Hope you like it.



Posted by: MR. JOYDEEP DAS

4 comments:

  1. Use a table-valued parameter.

    ReplyDelete
    Replies
    1. Thanks "marc-jellinek"
      Yes you can in SQL 2008. I am trying to post an article related to it. I am requesting you to join my site.

      Delete
  2. Uset defined table types solved that problem in SQL 2008. Before that you had to do" the float the temp table through the nested stored proc" technique.

    ReplyDelete
    Replies
    1. Thanks "The Highwayman"
      Please read the article mentioned bellow.
      http://sqlknowledgebank.blogspot.in/2012/05/table-valued-parameters.html

      I am also requesting you to join my site in my web

      Delete