CODEWITHSUNDEEP
sqlsql-server
XiB
XiB

Reputation: 760

Stored procedure with table valued parameter

I am trying to make a stored procedure that uses a table as input parameter. However it is not working as intended. Steps I've taken:

First I make a user defined table type:

CREATE TYPE y_yhat_tabletype AS TABLE
(
    UTC_DT datetime,
    y  float,
    yhat float
);

Then I try to make a stored procedure:

create procedure sp_evaluate_y_yhat 
    @data y_yhat_tabletype readonly
    as
    begin
        select sum(@data.y) as sum_y, sum(@data.yhat) as sum_yhat
    end

But I get the error:

Msg 137, Level 16, State 1, Procedure sp_evaluate_y_yhat, Line 12 [Batch Start Line 0]
Must declare the scalar variable "@data".
Msg 137, Level 16, State 1, Procedure sp_evaluate_y_yhat, Line 12 [Batch Start Line 0]
Must declare the scalar variable "@data".

The select sum(@data.y) as sum_y, sum(@data.yhat) as sum_yhat line is just to get some output while testing and will be replaced with more operations as soon as I get this to work.

Any idea on what I'm missing?

Upvotes: 0

Views: 1025

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300559

@data is essentially a table, so you need to treat it as such.

Declare as:

create procedure sp_evaluate_y_yhat
( 
    @data y_yhat_tabletype readonly
)
as
    begin
        select sum(y) as sum_y, sum(yhat) as sum_yhat
        from @data
    end
go

It's worth noting that if @data contained many rows, the row estimate might be off as table variables don't get statistics.

Upvotes: 4

Related Questions