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/09/12)

Introduction

SQL Server 2005 and previous versions do not support passing a table variable to a stored procedure. In one of my previous articles, I had presented a way to pass a table to a stored procedure. There had been a large number of excellent comments in the discussion forum on this subject and a few alternate methods were discussed.

This article introduces the new feature added to SQL Server 2008, which supports passing a TABLE to a stored procedure or function.

The CODE

This article is based on SQL Server 2008 CTP 3. Some of the information may change by the time the product is finally released.

Before we create a Function or Stored Procedure that accepts a TABLE variable, we need to define a User Defined TABLE Type. SQL Server 2008 introduced a new User defined TABLE type. A TABLE type represents the structure of a table that can be passed to a stored procedure or function.

So the first step is to create a User Defined TABLE type. The following TSQL code creates a User defined TABLE type named "ItemInfo".

    1 CREATE TYPE ItemInfo AS TABLE

    2 (

    3     ItemNumber VARCHAR(50),

    4     Qty INT

    5 )

You can use the system view SYS.TYPES to see the type that you have just created. The following query returns all the types defined in the system.

    1 SELECT * FROM SYS.TYPES

    2 

    3 /*

    4     If you just need to find information about the TABLE types, you could find it from

    5     the following TSQL query.

    6 */

    7 

    8 SELECT * FROM SYS.TYPES WHERE is_table_type = 1

    9 

   10 /*

   11     There is another view, which is handy to find information about TABLE types.

   12 */

   13 

   14 SELECT * FROM SYS.TABLE_TYPES

We have created a TABLE type that we need. Now let us see how it works. Let us create a variable of type "ItemInfo" and try to insert a few records to it. Then lets query the table variable to see if the information is correctly inserted. [code]

    1 /*

    2     Let us declare a variable of type ItemInfo which is a TABLE Type

    3 */

    4 DECLARE @items AS ItemInfo

    5 

    6 /*

    7     Insert values to the variable

    8 */

    9 

   10 INSERT INTO @Items (ItemNumber, Qty)

   11     SELECT '11000', 100 UNION ALL

   12     SELECT '22000', 200 UNION ALL

   13     SELECT '33000', 300

   14 

   15 /*

   16     Lets check if the values are correctly inserted or not

   17 */

   18 SELECT * FROM @Items

   19 

   20 /*

   21 OUTPUT:

   22 

   23 ItemNumber                                         Qty

   24 -------------------------------------------------- -----------

   25 11000                                             100

   26 22000                                             200

   27 33000                                             300

   28 */

Now let us create a stored procedure that accepts a TABLE variable. Let us create a very simple stored procedure which accepts a TABLE variable and SELECTs contents of the table.

    1 CREATE PROCEDURE TableParamDemo

    2 (

    3     @Items ItemInfo

    4 )

    5 

    6 AS

    7 

    8 SELECT *

    9 FROM @Items

Well, this would generate the following error:

    1 /*

    2 Msg 352, Level 15, State 1, Procedure TableParamDemo, Line 1

    3 The table-valued parameter "@Items" must be declared with the READONLY option.

    4 */

A table variable that is passed to a stored procedure or function should be marked as READONLY. The "callee" cannot modify the table being passed into it. Here is the correct code.

    1 CREATE PROCEDURE TableParamDemo

    2 (

    3     @Items ItemInfo READONLY

    4 )

    5 

    6 AS

    7 

    8 SELECT *

    9 FROM @Items

Now let us execute the stored procedure we just created. Run the following code.

    1 /*

    2     declare the variable

    3 */

    4 DECLARE @items AS ItemInfo

    5 

    6 /*

    7     Insert values to the variable

    8 */

    9 

   10 INSERT INTO @Items (ItemNumber, Qty)

   11     SELECT '11000', 100 UNION ALL

   12     SELECT '22000', 200 UNION ALL

   13     SELECT '33000', 300

   14 

   15 /*

   16     Execute the procedure

   17 */

   18 EXECUTE TableParamDemo @Items

   19 

   20 /*

   21 OUTPUT:

   22 

   23 ItemNumber                                         Qty

   24 -------------------------------------------------- -----------

   25 11000                                             100

   26 22000                                             200

   27 33000                                             300

   28 

   29 */

You cannot modify the TABLE parameter passed into the stored procedure. If you try to do so, you will get an error as shown in the following example.

    1 CREATE PROCEDURE TableParamDemo

    2 (

    3     @Items ItemInfo READONLY

    4 )

    5 

    6 AS

    7 

    8 SELECT *

    9 FROM @Items

   10 

   11 INSERT INTO @Items (ItemNumber, Qty)

   12     SELECT '1001', 20

   13 

   14 /*

   15 OUTPUT:

   16 

   17 Msg 10700, Level 16, State 1, Procedure TableParamDemo, Line 11

   18 The table-valued parameter "@Items" is READONLY and cannot be modified.

   19 */

Conclusions

The support for TABLE variables is very interesting. While working with User Defined TABLE Type, please note that you cannot use it as a column of a table. Please also note that, once created, you cannot alter the structure of the TABLE.

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

Create Variable Dynamically

Create Variable Dynamically in Stored Procedure

FORUM

Creating view from stored procedure results

Creating view from stored procedure results

FORUM

Persistent variables between stored procedure calls

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

FORUM

Dynamic SQL issues with passing variables in stored procedure

stored procedure can't handle variable in sql string

FORUM

variable columns in select statements?

want to create select's with variable column names

Tags
news    
sql server 2008    
 
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