Hello friends, I apologies for not writing any article since a long time (Almost a month). I was too busy and did not get a change to write an article since a long time. Today I am back with one new feature of SQL Server 2008 i.e. Table valued parameters which can be passed to a stored procedure or function. Lets have a look on benefits and restrictions of table valued parameters.
Benefits: We can get following benefits by using table valued parameters.
- Do not acquire locks for the initial population of data from a client.
- Are cached like a temp table when used in a stored procedure.
- We can get better performance than temporary tables or other ways to pass a list of parameters.
- SQL Server does not maintain statistics on columns of table-valued parameters.
- 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.
- You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.
Lets take an example to see how we can use table valued parameters in a stored procedure. First of all we will create a “Table” type, using below SQL statement.
Create type Employee as table(eid int,name varchar(30))
Lets create a table “Emp” using below SQL Statement.
Create table Emp(Emp_Id int,name varchar(30))
Now we will create a stored procedure in which we will pass the table valued parameter to insert rows into “Emp” table. We will use below SQL Statement to create the stored procedure.
Create Procedure Usp_Insert_EmpDetail(@empTbl as Employee readonly)
insert into Emp(Emp_id,name) select eid,name from @emptbl
Lets execute below SQL Statements to execute stored procedure “Usp_Insert_EmpDetail” and insert few rows into “Emp” table.
Declare @tmp as Employee
insert into @tmp values(101,’Neeraj Yadav’)
insert into @tmp values(102,’Ram Sharma’)
insert into @tmp values(103,’Dheeraj Prakash’)
Exec Usp_Insert_EmpDetail @tmp
In the above SQL Statements, first line is the declaration of table type variable (@tmp) and we have inserted three rows into table variable. In last line we are executing the stored procedure with table valued parameters.
Table valued parameters are useful when we have huge no of parameters to pass in a stored procedure or function. In that scenario we can avoid declaring too many parameters and just pass table valued parameter and each column can contain the value of each parameter.
Lets see the results after executing the above SQL Statements.
In the above screenshot, we can see that last SQL Statement is a select statement where we are selecting all rows from “Emp” table. I hope you will find this article useful and please get back to me in case you have any confusion.
661 total views, 3 views today