0

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
Khaled Nasser
  • 23
  • 3

1 Answers1

1

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>.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Yes, that works fine thank you , I tried it before, but how can I use the function in View? – Khaled Nasser Feb 03 '23 at 15:13
  • You won't be able to, @KhaledNasser . – Thom A Feb 03 '23 at 15:18
  • @KhaledNasser, since views can't be parameterized, you would need to incorporate the view queries into the TVF instead of the other way around. – Dan Guzman Feb 03 '23 at 15:19
  • Alternatively, you might want to consider an inline table value function that doesn't use a table type parameter, and instead uses scalar values; difficult to know what they would like like though. – Thom A Feb 03 '23 at 15:22