Table-Valued Parameters: is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.
Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:
1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.
--Create a Table Named
CREATE TABLE EMPLOYEE(SALARY INT)
--Insert data ..
INSERT dbo. EMPLOYEE(SALARY) VALUES (1000),(2000),(3000),(4000)
--Create a SQL SERVER TYPE AS TABLE --
CREATE TABLE EMP_DETAILS(SALARY INT)
--Create a ProcedureCREATE PROC NEW_EMPLOYEE(@NEW_EMP AS EMPLOYEE READONLY)
AS INSERT INTO EMP_DETAILS SELECT * FROM @NEW_EMP;
--Execution of table valued parameters
DECLARE @NEW_EMP AS EMPLOYEE
INSERT INTO @NEW_EMP(SALARY) SELECT SALARY FROM EMPLOYEE
EXEC NEW_EMPLOYEE @NEW_EMP;
--Now verify the data...
SELECT * FROM EMP_DETAILS
Regards
Ram
_________________________________________________________________________________________
