Saturday, November 17, 2018

How to pass table result as a parameter in stored procedure

---> Create user defined datatype,table type object

CREATE TYPE StateMaster AS TABLE
(
 StateCode VARCHAR(2),
 StateDescp VARCHAR(250)
)
GO

create table StateMst(
StateCode VARCHAR(2),
 StateDescp VARCHAR(250),
 todaydate datetime)
--> Use user defined type object as parameter in stored procedure

CREATE PROCEDURE TableValuedParameterExample_SP
(
@TmpTable StateMaster READONLY
)
AS
BEGIN
 INSERT INTO StateMst
  SELECT StateCode,StateDescp,getdate() 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

--select * from StateMst