Table value parameters are not available in the earlier versions of sql server.
This is the new feature introduced in sql server 2008.
Advantages:
1) we can reduce no of lines of code
2)Reduce parameter list that we send to stored procedure or function.
3)It is used to send multiple rows of data to stored procedure or function.
To use Table Valued Parameters in function or stored procedure , follow these steps:
1)Create User Defined Table Type.( with table structure)
2)Declare a variable of this User Defined Table Type .
3)Insert some rows into this Table Type
4)Pass this variable as a parameter to stored procedure or function.
Note:
1) User Defined Table Type can only be used as READONLY parameter.
2)Only select operation can be performed on it.
3)INSERT , UPDATE DELETE ,SELECT * INTO operations can not be performed.
Example : Passing Table Valued Parameter to a function
/* CREATE USER DEFINED TABLE TYPE */
CREATE TYPE StateMaster AS TABLE
(
StateCode VARCHAR(2),
StateDescp VARCHAR(250)
)
GO
/*CREATE FUNCTION WHICH TAKES TABLE AS A PARAMETER */
CREATE FUNCTION TableValuedParameterExample(@TmpTable StateMaster READONLY)
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE @StateDescp VARCHAR(250)
SELECT @StateDescp = StateDescp FROM @TmpTable
RETURN @StateDescp
END
GO
BEGIN
/* DECLARE VARIABLE OF TABLE USER DEFINED TYPE */
DECLARE @MyTable StateMaster
/* INSERT DATA INTO TABLE TYPE */
INSERT INTO @MyTable VALUES('11','AndhraPradesh')
INSERT INTO @MyTable VALUES('12','Assam')
/* INVOKE FUNCTION */
SELECT elekha.TableValuedParameterExample(@MyTable)
GO
Example : Passing Table Valued Parameter to Stored Procedure
/*CREATE STORED PROCEDURE WHICH TAKES TABLE AS A PARAMETER */
CREATE PROCEDURE TableValuedParameterExample_SP
(
@TmpTable StateMaster READONLY
)
AS
BEGIN
INSERT INTO StateMst
SELECT * FROM @TmpTable
END
GO
BEGIN
/* DECLARE VARIABLE OF TABLE USER DEFINED TYPE */
DECLARE @MyTable StateMaster
/* INSERT DATA INTO TABLE TYPE */
INSERT INTO @MyTable VALUES('11','AndhraPradesh')
INSERT INTO @MyTable VALUES('12','Assam')
/* EXECUTE STORED PROCEDURE */
EXEC TableValuedParameterExample_SP @MyTable
GO
Neat, Nice and simple solution. I liked it. Keep up the good work....(y).
ReplyDeleteWhat if: I want to used the passed in table variable in a JOIN?
ReplyDeleteCREATE PROCEDURE TableValuedParameterExample_SP
(
@TmpTable StateMaster READONLY
)
AS
BEGIN
INSERT INTO StateMst
SELECT * FROM @TmpTable t
JOIN OtherTable o
ON t.StateCode = o.StateCode
END
GO
This doesn't work. Compiles just fine, but at run time you get an error:
Msg 208, Level 16, State 1, Line 194
Invalid object name 't'.
Never mind...I figured it out:
ReplyDelete(SELECT * FROM @TmpTable) t
JOIN OtherTable o
ON t.StateCode = o.StateCode
How check if exists orr not TYPE AS TABLE and FUNCTION, and DROP if exists?
ReplyDeleteUseful:
EXEC tempdb.dbo.sp_help @objname = N'#temp';
https://stackoverflow.com/questions/7486941/finding-the-data-types-of-a-sql-temporary-table