Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Passing a Table to A Stored Procedure

By Jacob Sebastian, (first published: 2007/05/30)

Introduction

Most of us would be very specific in designing the database code (Stored procedures, functions, views etc) in a re-usable and manageable manner. It is particularly important when the application is large. Code for common functionalities should be identified and moved to a function that can be called from different parts of the application. The same may be done with views, stored procedures etc. Designing the code in such a manner increases the manageability of the code as well as provides greater re-usability, and thus better productivity and lesser bugs.

Some times, while attempting to achieve the above, we would come across certain hurdles due to the limitations of TSQL. At times we feel that TSQL does not really give us enough freedom like other application development platforms. In this article, I am trying to present such a case where a re-usable function is created to which a table can be passed as an argument.

The Problem

Let us say, we are working on an Inventory Management System. When a transaction (sales order, invoice, receipt of goods, inventory adjustment etc) takes place, we need to update the available inventory of the items affected by the transaction. We already have a stored procedure to save/update each transaction. Each of those stored procedures needs to update the inventory of all the items affected by the current transaction.

Please note that, the word 'Transaction' above, does not refer to Database Transactions. They refer to the various Inventory Operations supported by the application.

Since the inventory needs to be updated from different places, it makes sense to move that part of the code to a separate stored procedure. Then this new stored procedure needs to be called from different places from where the inventory is to be updated. So far it looks simple. But the difficult part is to pass the items to be updated.

A TABLE variable would look to be the ideal solution. If we could pass a TABLE variable containing the list of items to be updated, then the complexity can be reduced to a great extend. But SQL Server does not allow to pass a TABLE variable as a parameter to a stored procedure. So what is the next option?

In this article, I am trying to present a solution to the above scenario by using XML as the format to pass a table to a stored procedure. The CALLER can transform the table (Query result) to an XML variable and pass to the stored procedure. The CALLEE can either convert the XML parameter back to a TABLE variable or directly use XQuery on the XML variable.

The Caller

The CALLER should transform the table to an XML variable. The DATA may come from a table or a query. The following example shows how to create an XML variable from the results of a query. 

    1 /*

    2     Let us first create sample table.

    3 */

    4 

    5 CREATE TABLE [dbo].[OrderDetails](

    6     [OrderDetailID] [int] IDENTITY(1,1) NOT NULL,

    7     [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    8     [Qty] [int] NULL

    9 ) ON [PRIMARY]

   10 

   11 /*

   12     Populate the sample table with values

   13 */

   14 INSERT INTO OrderDetails(ItemNumber, Qty)

   15     SELECT 'A001', 10

   16     UNION SELECT 'A002', 20

   17     UNION SELECT 'A003', 30

   18 /*

   19     The query below returns the results in XML format.

   20 */

   21 

   22 SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW('item'), ROOT('items')

   23 

   24 /*

   25 OUTPUT:

   26 

   27 <items>

   28   <item ItemNumber="A001" Qty="10" />

   29   <item ItemNumber="A002" Qty="20" />

   30   <item ItemNumber="A003" Qty="30" />

   31 </items>

   32 */


In the article Advanced XML Processing - II, I have presented a few detailed examples which demonstrate the different ways to generate and format query results as XML.

Now, let us assign the resultant XML value to an XML variable. [code]

    1 -- Declare the variable

    2 DECLARE @x XML

    3 

    4 -- store the results of the Query to XML variable

    5 SET @x = (SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW('item'), ROOT('items'), TYPE)

    6 

    7 -- select the values from the XML variable (to make sure that we did it correctly)

    8 SELECT

    9     x.item.value('@ItemNumber[1]', 'VARCHAR(20)') AS ItemNumber,

   10     x.item.value('@Qty[1]', 'INT') AS Qty

   11 FROM @x.nodes('//items/item') AS x(item)

At this stage, we have an XML variable ready, which we could pass to a child procedure/function. The XML variable contains the values that we want the child procedure/function to process/update. The child procedure can either transform the XML variable back to a TABLE or it can directly read the values from the XML variable.

The Callee

So far, we have seen how to create an XML variable from the results of a query. This XML variable can be passed to another stored procedure which can update the inventory data based on the item information passed to the procedure. The simplest way is to create a wrapper view around the XML variable and use it as if it is a table.

Let us create another sample table, Inventory, which will be updated with the information passed through the XML parameter. The following script will create the sample table.

    1 CREATE TABLE [dbo].[Inventory](

    2     [InventoryID] [int] IDENTITY(1,1) NOT NULL,

    3     [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    4     [Stock] [int] NULL

    5 ) ON [PRIMARY]

    6 

    7 INSERT INTO Inventory (ItemNumber, Stock)

    8     SELECT 'A001', 0

    9     UNION SELECT 'A002', 0

   10     UNION SELECT 'A003', 0

The following sample code shows the implementation needed at the side of the 'callee'.

    1 CREATE PROCEDURE [dbo].[UpdateInventory1]

    2 (

    3     @x XML

    4 )

    5 AS

    6 

    7 SET NOCOUNT ON

    8 

    9 /*

   10     The code below creates a wrapper view around the XML variable and updates the

   11     "inventory" table with the information.

   12 */

   13 

   14 UPDATE Inventory SET

   15     stock = stock + v.Qty

   16 FROM Inventory inv

   17 INNER JOIN (

   18     SELECT

   19         x.item.value('@ItemNumber[1]','varchar(20)') AS ItemNumber,

   20         x.item.value('@Qty[1]','INT') AS Qty

   21     FROM @x.nodes('//items/item') AS x(item)

   22 ) v ON (v.ItemNumber = inv.ItemNumber)

   23 

   24 RETURN

Execute

Let us execute the procedure now. Run the following code.

    1 -- Declare the variable

    2 DECLARE @x XML

    3 

    4 -- store the results of the Query to XML variable

    5 SET @x = (SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW('item'), ROOT('items'), TYPE)

    6 

    7 -- execute the stored procedure

    8 EXECUTE UpdateInventory1 @x

    9 

   10 -- review the results

   11 SELECT * FROM inventory

Updated Procedure

The sample code above, creates a wrapper view around the XML variable. This is a pretty simple and straight-forward approach. You could still access the values as if it is coming from a table/view. The complexity of XML processing is absorbed in the inner view.

The example below, demonstrates another syntax, which updates the table directly from the XML variable.

    1 CREATE PROCEDURE [dbo].[UpdateInventory2]

    2 (

    3     @x XML

    4 )

    5 AS

    6 

    7 SET NOCOUNT ON

    8 

    9 /*

   10     This version of the stored procedure has a slightly enhanced version of the

   11     TSQL code. This version updates the table directly from the XML variable,

   12     rather than converting the XML data to a view.

   13 */

   14 

   15 UPDATE Inventory SET

   16     stock = stock + x.item.value('@Qty[1]','INT')

   17 FROM Inventory inv

   18 INNER JOIN @x.nodes('//items/item') x(item) ON

   19     (x.item.value('@ItemNumber[1]','varchar(20)') = inv.ItemNumber)

   20 

   21 RETURN

Conclusions

In the past few years, several times I came across the situation where I needed a way to pass a table to a function or stored procedure. Since SQL Server does not allow to pass a TABLE variable to a function or stored procedure, the only way I could make it work is by using the approach presented above. There may be other ways to get this done too. It is apparent that there will be a small performance penalty by doing this. I did not do extensive tests to see if there is a performance problem. I did not notice any performance issues in my applications so far.

Total article views: 65348 | Views in the last 30 days: 166
 
Related Articles
FORUM

Store Procedure to update

Store Procedure to update

FORUM

Insert Update Stored Procedure - HELP

Problem with insert update stored procedure

FORUM

Persistent variables between stored procedure calls

Global variables, local variables to a session, temporary tables from a stored procedure, other sugg...

SCRIPT

Insert Update Stored Procedure for a table

Generic Script for Insert Update Stored Procedures

FORUM

The value of variable is disappearing in stored procedure

The value of variable is disappearing in stored procedure

Tags
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones