Pass Table as a Parameter In SQL StoreProcedure

Tuesday, June 26, 2012

/* Create a table type. This table behave like a user define Variable */
create TYPE ASROYTableType AS TABLE
( sl INT
, tr VARCHAR(10)
 ,ptype nvarchar(2));
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE UpdatePrintSt
    (
    @TTP ASROYTableType READONLY
    --@type nvarchar(2)
    )
    AS
    begin
SET NOCOUNT ON
declare @pt nvarchar(2)
set @pt=(select top 1 ptype from @TTP)
--select @pt=tr from @TTP
if(@pt ='2')
update Tbl_Sap_master set prnt='Y'where TR_No in(select tr from @TTP)
else
update Inova_PIS_Master set prnt='Y'where TR_No in(select tr from @TTP)

    end


0 comments:

Post a Comment