#sql table

New Post has been published on http://www.varindersandhu.in/2011/06/11/sql-server-table-valued-parameters/

SQL Server - Table-Valued Parameters

Table-valued parameters are a new parameter type introduced in SQL Server 2008. With the help of table-valued parameters, you can send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters.

Steps to create Table-Valued Parameters

Create a table type and define the table structure.

Declare a stored procedure or function that has a parameter of the table type.

Declare a variable of the table type, and reference the table type.

Fill the table variable by using an INSERT statement.

Now you can pass the variable to a procedure or function.

Now let ‘s try to understand with example

Create a table as below

USE testdb Go CREATE TABLE Users ( UserID INT, UsertName VARCHAR(255) ) GO

1    Create a table type and define the table structure

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

CREATE TYPE UserType AS TABLE ( UserId INT, UserName VARCHAR(255) ) GO

2    Declare a stored procedure or function that has a parameter of the table type

CREATE PROCEDURE InsUser @InsUser UserType READONLY AS INSERT INTO Users(UserID,UsertName) SELECT * FROM @InsUser GO

3    Declare a variable of the table type, and reference the table type

DECLARE @User AS UserType

4    Fill the table variable by using an INSERT statement

INSERT INTO @User(UserId,UserName) VALUES (1,'Varinder'), (2,'Dinesh'), (3,'Vikram'), (4,'Ranjoyt'), (5,'Ram');

5    Now you can pass the variable to a procedure or function

EXEC InsUser @User; GO

Result :

See the values in tables

Note:

We created @InsUser parameter read only because Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

New Post has been published on http://www.varindersandhu.in/2011/06/11/sql-server-table-valued-parameters/

SQL Server - Table-Valued Parameters

Table-valued parameters are a new parameter type introduced in SQL Server 2008. With the help of table-valued parameters, you can send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters.

Steps to create Table-Valued Parameters

Create a table type and define the table structure.

Declare a stored procedure or function that has a parameter of the table type.

Declare a variable of the table type, and reference the table type.

Fill the table variable by using an INSERT statement.

Now you can pass the variable to a procedure or function.

Now let ‘s try to understand with example

Create a table as below

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

USE testdb Go CREATE TABLE Users ( UserID INT, UsertName VARCHAR(255) ) GO

1    Create a table type and define the table structure

CREATE TYPE UserType AS TABLE ( UserId INT, UserName VARCHAR(255) ) GO

2    Declare a stored procedure or function that has a parameter of the table type

CREATE PROCEDURE InsUser @InsUser UserType READONLY AS INSERT INTO Users(UserID,UsertName) SELECT * FROM @InsUser GO

3    Declare a variable of the table type, and reference the table type

DECLARE @User AS UserType

4    Fill the table variable by using an INSERT statement

INSERT INTO @User(UserId,UserName) VALUES (1,'Varinder'), (2,'Dinesh'), (3,'Vikram'), (4,'Ranjoyt'), (5,'Ram');

5    Now you can pass the variable to a procedure or function

EXEC InsUser @User; GO

Result :

See the values in tables

Note:

We created @InsUser parameter read only because Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.