SQL function create error - "Must declare the scalar variable"

Question

I'm trying to create this function which returns a table and has a table name as a parameter:

CREATE FUNCTION GetCostData(@CostTable VARCHAR(30))
RETURNS @H2 TABLE (
    Repairdate    datetime     NOT NULL,
    ReceivedDate  datetime     NOT NULL
)  
AS
BEGIN 
    INSERT INTO @H2(DataFactura, Data)
      SELECT R1.Repairdate, Max(H1.ReceivedDate) as ReceivedDate
        FROM @CostTable R1
        JOIN History H1 
        ON R1.VehicleID=H1.VehicleID 
        AND H1.ReceivedDate < R1.RepairDate
        GROUP BY R1.RepairDate;
    RETURN;
END;

But I get 'Must declare the scalar variable "@CostTable".' error. Why? How can I fix it?

`CostTable ` is `VARCHAR`, you might want to use dynamic sql. — Tim Schmelter, Nov 29 '13 at 09:41
You cannot use dynamic SQL in functions, I think... You will need to change that into a stored proc. — Szymon, Nov 29 '13 at 09:44
beware of what you are doing. Generic functions like that, especially if you use them in another SELECT, will drain performances. Sometimes, in SQL, it is better to copy/paste code — rudi bruchez, Nov 29 '13 at 09:47
Thanks, but I can't turn it into a SP, because I'm using it in a query. — milo2011, Nov 29 '13 at 10:04

score 1 · Answer 1 · answered Apr 14 '14 at 15:08

You're missing the DECLARE @CostTable statement

Try this code:

CREATE FUNCTION GetCostData(@CostTable VARCHAR(30))
RETURNS @H2 TABLE (
    Repairdate    datetime     NOT NULL,
    ReceivedDate  datetime     NOT NULL
)

AS

BEGIN 
DECLARE @CostTable table (RepairDate datetime not null) 
    INSERT INTO @H2(DataFactura, Data)
      SELECT R1.Repairdate, Max(H1.ReceivedDate) as ReceivedDate
        FROM @CostTable R1
        JOIN History H1 
        ON R1.VehicleID=H1.VehicleID 
        AND H1.ReceivedDate < R1.RepairDate
        GROUP BY R1.RepairDate;
    RETURN;
END;

Hope this helps

Szymon · Answer 2 · 2013-11-29T23:58:45.857

As you cannot use dynamic SQL in functions, it will be better to turn it into a stored procedure:

CREATE PROCEDURE GetCostData(@CostTable VARCHAR(30)) 
AS
BEGIN 
  DECLARE @sql NVARCHAR(1000) =
  N'SELECT R1.Repairdate, Max(H1.ReceivedDate) as ReceivedDate
    FROM ' + @CostTable + N'R1
    JOIN History H1 
    ON R1.VehicleID=H1.VehicleID 
    AND H1.ReceivedDate < R1.RepairDate
    GROUP BY R1.RepairDate'

    EXEC (@sql)
END

To use the result of this stored proc, you should insert the results into a temp table and then join to this temp table. See this SQL Fiddle.

create table table1(id int, Repairdate datetime)

insert into table1 values (1, getdate())

go

create procedure GetCostData
as
begin
  select getdate() as Repairdate, getdate() as ReceivedDate
end

go

create table #temp(Repairdate datetime, ReceivedDate datetime)

insert into #temp
exec getcostdata

select * from table1 t1
inner join #temp t
on cast(t1.Repairdate as date) = cast(t.Repairdate as date)

Thanks, but I can't turn it into a SP, because I'm using it in a query. — milo2011, Nov 29 '13 at 10:04
See here: http://stackoverflow.com/questions/19751718/three-tables-sql-query/20260641?noredirect=1#comment30255131_20260641 — milo2011, Nov 29 '13 at 10:14

SQL function create error - "Must declare the scalar variable"

2 Answers2