SQL Server make table as a function parameter

Question

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

Dan Guzman · Accepted Answer · 2023-02-03 14:19:18Z

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

answered Feb 3, 2023 at 14:19

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Khaled Nasser Over a year ago

Yes, that works fine thank you , I tried it before, but how can I use the function in View?

Thom A Over a year ago

You won't be able to, @KhaledNasser .

Dan Guzman Over a year ago

@KhaledNasser, since views can't be parameterized, you would need to incorporate the view queries into the TVF instead of the other way around.

Thom A Over a year ago

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.

Collectives™ on Stack Overflow

SQL Server make table as a function parameter

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related