Pass Collection to Function or Procedure

0

Category :


IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'TestPassList')
 DROP TYPE TestPassList
GO

CREATE TYPE dbo.TestPassList
AS TABLE
(
 BookingIdMaster INT,
 BookingId  INT
);
GO


CREATE PROCEDURE sp_TestPassList
  @List AS dbo.TestPassList READONLY
AS
BEGIN
 SELECT *
  FROM @List

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'fn_TestPassList') AND type in (N'P', N'TF'))
 DROP FUNCTION fn_TestPassList
GO

CREATE FUNCTION fn_TestPassList(@List AS dbo.TestPassList READONLY)
RETURNS @temptable 
 TABLE (  
   BookingId INT
 )       
AS 
BEGIN 
  
 INSERT INTO @temptable    
  SELECT BookingId
  FROM @List
 
 RETURN      
END
GO

----------- test
DECLARE @TestPassListVar dbo.TestPassList
INSERT INTO @TestPassListVar 
SELECT TOP 10 billing_id Booking_IdMaster,
    Booking_Id
 FROM d_booking
 
SELECT * 
 FROM fn_TestPassList(@TestPassListVar)
 

0 comments:

Post a Comment