Passing a Table to a Stored Procedure

Introduction

SQL Server 2005 and previous versions do not support passing a table variable to a stored procedure

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.

coldfusion 8 pdf download

>>Download coldfusion 8 pdf
coldfusion 8 new features
Adobe Flex integration

ColdFusion 8 now includes Adobe LiveCycle® Data Services ES (formerly Flex™ Data Services) and other data exchange improvements to simplify the data-enabling of rich Internet applications (RIAs) you build with ColdFusion and Flex.

Image manipulation

ColdFusion 8 adds more than 50 new tags and functions for creating and manipulating images, from simple to sophisticated. For example, the new CFIMAGE tag provides shortcuts to the most common image actions, including reading, writing, resizing, rotating, and converting images.

Atom and RSS feeds

ColdFusion 8 introduces the new CFFEED tag, which can read and create RSS and Atom feeds in commonly used formats, so you can quickly and easily create complex content syndication applications.

Improved file manipulation functions

ColdFusion 8 adds several new file manipulation functions to the ColdFusion Markup Language (CFML), most notably the ability to read and write large files in defined segments, rather than in a single operation.

JavaScript operators in CFML

ColdFusion 8 supports common JavaScript operators such as ++, ==, ||, <, and > so they can be used in CFML expressions.

CFC improvements

ColdFusion 8 adds several highly requested improvements to ColdFusion components (CFCs), including the ability to create interface definitions, use J2EE session replication in a cluster, have access to the CFCs in session data across all the machines in a cluster, and use the new onMissing Method function

Reporting enhancements

ColdFusion 8 introduces a completely new version of the ColdFusion Report Builder. Use it to create high-quality, structured reports with more output choices and a more consistent look and feel throughout your applications. You can also create and apply cascading style sheet (CSS) definitions to reports, export your reports in HTML or XML format, and create report styles for all of your reports.

Database interaction improvements

ColdFusion 8 provides a new DBINFO tag to programmatically access information about a data source, allows stored procedures and queries using CFQUERYPARAM to be cached, and much more. Plus, there is expanded database driver support with new options for SQL Server 2005, Oracle 10g, Sybase ASE15, MySQL 4 and 5, PostgreSQL 8, and others.

Array and structure creation improvements

ColdFusion 8 allows you to create arrays and structures implicitly in assignment statements without using the ArrayNew or Structnew functions. The result is cleaner code with less typing

Download coldfusion 8 pdf