Suggested Videos
Part 96 - Logon triggers in sql server
Part 97 - Select into in sql server
Part 98 - Difference between where and having in sql server
In this video we will discuss table valued parameters in SQL Server.
Table Valued Parameter is a new feature introduced in SQL SERVER 2008. Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. Prior to SQL SERVER 2008, it is not possible to pass a table variable as a parameter to a stored procedure.
Let us understand how to pass multiple rows to a stored procedure using Table Valued Parameter with an example. We want to insert multiple rows into the following Employees table. At the moment this table does not have any rows.
SQL Script to create the Employees table
Step 1 : Create User-defined Table Type
Step 2 : Use the User-defined Table Type as a parameter in the stored procedure. Table valued parameters must be passed as read-only to stored procedures, functions etc. This means you cannot perform DML operations like INSERT, UPDATE or DELETE on a table-valued parameter in the body of a function, stored procedure etc.
Step 3 : Declare a table variable, insert the data and then pass the table variable as a parameter to the stored procedure.
That's it. Now select the data from Employees table and notice that all the rows of the table variable are inserted into the Employees table.
In our next video, we will discuss how to pass table as a parameter to the stored procedure from an ADO.NET application
Part 96 - Logon triggers in sql server
Part 97 - Select into in sql server
Part 98 - Difference between where and having in sql server
In this video we will discuss table valued parameters in SQL Server.
Table Valued Parameter is a new feature introduced in SQL SERVER 2008. Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. Prior to SQL SERVER 2008, it is not possible to pass a table variable as a parameter to a stored procedure.
Let us understand how to pass multiple rows to a stored procedure using Table Valued Parameter with an example. We want to insert multiple rows into the following Employees table. At the moment this table does not have any rows.
SQL Script to create the Employees table
Create Table Employees
(
Id int primary
key,
Name nvarchar(50),
Gender nvarchar(10)
)
Go
Step 1 : Create User-defined Table Type
CREATE TYPE EmpTableType AS TABLE
(
Id INT PRIMARY
KEY,
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
Go
Step 2 : Use the User-defined Table Type as a parameter in the stored procedure. Table valued parameters must be passed as read-only to stored procedures, functions etc. This means you cannot perform DML operations like INSERT, UPDATE or DELETE on a table-valued parameter in the body of a function, stored procedure etc.
CREATE PROCEDURE spInsertEmployees
@EmpTableType EmpTableType READONLY
AS
BEGIN
INSERT INTO
Employees
SELECT *
FROM @EmpTableType
END
Step 3 : Declare a table variable, insert the data and then pass the table variable as a parameter to the stored procedure.
DECLARE
@EmployeeTableType EmpTableType
INSERT INTO @EmployeeTableType VALUES
(1, 'Mark', 'Male')
INSERT INTO @EmployeeTableType VALUES
(2, 'Mary', 'Female')
INSERT INTO @EmployeeTableType VALUES
(3, 'John', 'Male')
INSERT INTO @EmployeeTableType VALUES
(4, 'Sara', 'Female')
INSERT INTO @EmployeeTableType VALUES
(5, 'Rob', 'Male')
EXECUTE spInsertEmployees @EmployeeTableType
That's it. Now select the data from Employees table and notice that all the rows of the table variable are inserted into the Employees table.
In our next video, we will discuss how to pass table as a parameter to the stored procedure from an ADO.NET application
Thanks Venkat Please could you Upload a video on Paging in SQl Server with implementation in asp.net.
ReplyDeletePaging is explained in gridview section part 55.
Deletehttp://csharp-video-tutorials.blogspot.in/2013/10/part-55-custom-sorting-and-paging-in.html