[SOLVED] SQL Server make table as a function parameter

SQL Server make table as a function parameter

I have this function, and I want to make the table '[SEJ_Reservation]' as a parameter to the function, to be able to use the function with another table, the function as below :

CREATE FUNCTION [dbo].[fn_Inhouse]()
RETURNS @TEM_INHOUSE TABLE (LogID int ,InHouseDate DATE)
AS
BEGIN
with 
 l0 as (select 0 v from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))v(v)), 
 l1 as (select 0 v from l0 a cross join l0 b),
 nums as (select n = Row_Number() over(order by @@Spid) from l1)

insert into @TEM_INHOUSE (LogID, InHouseDate)
select ro.LogID, DateAdd(day, -n, ro.C_OUT) as InHouseDate 
from [dbo].[SEJ_Reservation] ro
join nums n on n.n <= ro.Gun;
  RETURN;
END;

I try something like that :

CREATE TYPE TableType 
AS TABLE (LogID int,C_OUT datetime,Gun int)
GO 

CREATE FUNCTION [dbo].[fn_Inhouse5](@mytable TableType  READONLY)
RETURNS @TEM_INHOUSE TABLE (LogID int ,InHouseDate DATE)
AS
BEGIN
with 
 l0 as (select 0 v from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))v(v)), 
 l1 as (select 0 v from l0 a cross join l0 b),
 nums as (select n = Row_Number() over(order by @@Spid) from l1)

insert into @TEM_INHOUSE (LogID, InHouseDate)
select ro.LogID, DateAdd(day, -n, ro.C_OUT) as InHouseDate 
from @mytable ro
join nums n on n.n <= ro.Gun;
RETURN
END;
GO

SELECT * from  dbo.[fn_Inhouse5]('[SEJ_Reservation]')

but got message error:

 Operand type clash: varchar is incompatible with TableType

Solution

You need to declare the table type, insert rows, and pass it as a table-valued parameter for the function. T-SQL example:

DECLARE @TableType TableType;

INSERT INTO @TableType (LogID, C_OUT, Gun)
SELECT LogID, C_OUT, Gun
FROM SEJ_Reservation;

SELECT * from  dbo.[fn_Inhouse5](@TableType);

In .NET application code, you can pass the TVP rows as a DataReader, DataTable, or IEnumerable<SqlDataRecord>.