April 26, 2010 at 12:43 pm
Hi,
In my sproc#1 i am calling my TVP(table valued parameter) with a bunch of otehr param. towards the end, i need to call anotehr sproc and pass the same TVP to sproc#2
how will i do it? exec sproc2 @tvp tablevaluedparam ReadOnly
or is there another way of passing?
Thanks
April 27, 2010 at 1:57 pm
Here is a modification of the example from Books Online. It is really quite simple, which leads me to ask why you are looking for another way to do this.
/* Create a table type. */
CREATE TYPE ColorsType AS TABLE
( ColorID int identity(1,1) primary key
, Color varchar(20) )
GO
/* Create a sub-procedure to receive table-valued parameter and list its contents */
CREATE PROCEDURE stProcMinor
@NothingSpecialAboutTheParameterName ColorsType READONLY
AS
SET NOCOUNT ON
select * from @NothingSpecialAboutTheParameterName; -- list content of table-valued parameter
GO
/* Create a procedure to receive data for the table-valued parameter and pass it to the sub-proc. */
CREATE PROCEDURE stProcMajor
@TVP ColorsType READONLY
AS
SET NOCOUNT ON
EXEC stProcMinor @TVP; -- passcontrol to sup-procedure and passes the table-valued-parameter again
GO
----------------------------------------------------------------
--- Code to test the objects created above
----------------------------------------------------------------
/* Declare a table variable that references the ColorsType. */
DECLARE @Colors
AS ColorsType;
/* Add data to the table variable. */
INSERT INTO @Colors
Values ('RED'),('GREEN'),('YELLOW'),('BLUE');
/* Pass the table variable data to a stored procedure. */
EXEC stProcMajor @Colors
GO
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply