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