SQLserver2008 How to pass a table variable into a stored procedure

In Microsoft SQL Server , you can implement to pass a table variable into a stored procedure, and if the variable can be declared, it can be passed. Let's describe how to pass the table variables ( including the included data ) to the stored procedures and functions.

Pass-through table-valued parameters users often encounter many situations where a value container needs to be placed in a stored procedure rather than a single value. For most programming languages, it is common and necessary to pass container data structures to routines or pass them out.TSQLis no exception. SQL ServerthroughOPENXMLThis enables the user to store the data asVARCHARthe data type is then passed. to theSQL Server 2005, withXMLdata Types andXQuery, this feature becomes a bit easier. However, users still need toXMLdata is built and crushed to be able to use it, so this feature is not easy to use. SQL ServerThe ability to pass table-valued data types to stored procedures and functions greatly simplifies programming, because programmers don't have to be bothered to build and parseXMLthe data. This feature also allows client-side developers to deliver customer-side data forms toThe database.

How do I pass the table parameters ? take sales as an example, first set up a my saleshistory form, which contains information about the sale of the product. Write the following script to create the table of your choice in the database:

IF object_id (' saleshistory ') >0

DROP TABLE saleshistory;

GO

CREATE TABLE [dbo]. [Saleshistory] (

[Saleid] [INT] IDENTITY (1,1) not NULL PRIMARY KEY,

[Product] [varchar] (Ten) NULL,

[Saledate] [DateTime] NULL,

[Saleprice] [Money] NULL)

GO

Establishing table-valued parameters The first step is to create the exact table type, which is important because you can define the structure of the table in the database engine so that you can use the table in the process code when you need it. The following code creates a saleshistorytabletype table type definition:

CREATE TYPE Saleshistorytabletype as TABLE (

[Product] [varchar] (Ten) NULL,

[Saledate] [DateTime] NULL,

[Saleprice] [Money] NULL)

GO

If you want to see other types of table type definitions in your system, you can perform the following query command to view the system directory: SELECT * from Sys.table_types We need to define a stored procedure to handle table-valued parameters. The following program can accept table-valued parameters of the specified saleshistorytabletype type and load into saleshistory , and table-valued parameters are the value in the Product column is "Bigscreen":

CREATE PROCEDURE usp_insertbigscreenproducts (

@TableVariable Saleshistorytabletype READONLY)

As

BEGIN

INSERT into Saleshistory (Product, saledate, saleprice)

SELECT Product, saledate, saleprice

From @TableVariable WHERE Product = ' Bigscreen '

END

GO

The passed table variable can also be used as query data for any other table. Limitations of passing table-valued parameters the READONLY clause must be used when passing a table-valued variable into a program . The data in the table variable cannot be modified - You can use the data for any other action except for the modification. Also, you cannot make a table variable an OUTPUT parameter -it can only be used as an input parameter. Use your own new table variable type first, to declare a variable type saleshistorytabletype, you do not need to define the table structure again, because it is already defined when the table type is created. Here is the code snippet:

DECLARE @DataTable as Saleshistorytabletype

--the following script adds 1,records into my @DataTable table variable:

DECLARE @i SMALLINT

SET @i = 1

while (@i <=1000)

BEGIN

INSERT into @DataTable (Product,saledate,saleprice)

VALUES (' Computer ', DATEADD (mm,@i, ' 3/11/1919 '), DATEPART (Ms,getdate ()) + (@i + 57))

INSERT into @DataTable (Product, saledate, saleprice)

VALUES (' Bigscreen ', DATEADD (mm, @i, ' 3/11/1927 '), DATEPART (ms , GETDATE ()) + (@i +) )

INSERT into @DataTable (Product, saledate, saleprice)

VALUES (' pooltable ', DATEADD (mm, @i, ' 3/11/1908 '), DATEPART (ms , GETDATE ()) + (@i +))

SET @i = @i + 1

END

As long as the data is loaded into the table variable, the structure can be passed to the stored procedure. Note: When a table variable is passed as a parameter, the table is stored in thetempdbsystemdatabase, rather than passing the entire data set in memory. Because this ensures efficient processing of large quantities of data. All server side table variable parameter passing is done by using theReferencecalledtempdbtable in the. EXECUTE usp_insertbigscreenproducts @TableVariable = @DataTableto query whether the program is the same as expected, you can execute the following query to see if the record has been inserted intosaleshistoryin the table:SELECT * from SaleshistoryConclusion: AlthoughSQL ServerThere are some limitations to the use of the parameter passing function of the database, such as the inability to modify the data in the parameter and use the variable forOutput, but it has greatly improved the performance of the program, it can reduceServernumber of round trips, using table limits and extending programming inFeatures in the database engine.

SQLserver2008 How to pass a table variable into a stored procedure