Sep 7, 2023
SQL CREATE Table - Database tables in any RDBMS are used to store the data in the form of some structures (fields and records). Here, a fiel
seen from Israel
seen from Singapore
seen from United States
seen from Singapore
seen from Netherlands
seen from Bulgaria
seen from United States
seen from Singapore
seen from Singapore
seen from Singapore
seen from Brazil
seen from China
seen from China
seen from United States
seen from Philippines
seen from Russia
seen from Saudi Arabia
seen from Netherlands
seen from China
seen from Brazil
SQL CREATE Table - Database tables in any RDBMS are used to store the data in the form of some structures (fields and records). Here, a fiel
New Post has been published on Varinder Sandhu 's Blog
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.
For more detail : http://msdn.microsoft.com/en-us/library/bb510489.aspx
New Post has been published on Varinder Sandhu 's Blog
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.
For more detail : http://msdn.microsoft.com/en-us/library/bb510489.aspx