Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am altering function like:

ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY)
RETURNS TypePreviousListingResult 
AS 
BEGIN
    DECLARE @tbl_ListingDateDetails TypePreviousListingResult   
    RETURN @tbl_ListingDateDetails
END
GO

But throwing error as:

Msg 137, Level 16, State 1, Procedure fn_CalculateListing, Line 6
Must declare the scalar variable "@tbl_ListingDateDetails".

Why this giving error and asking for declaration even though I have declared that before using that variable?

TypePreviousListingResult is a table type created as CREATE TYPE TypePreviousListingResult AS TABLE....

Previous question: How to write function in sql which accept table as input and return result back as table?

share|improve this question
up vote 11 down vote accepted

There are two problems here:

  1. For Multistatement TVFs, you just need RETURN; instead of RETURN @variable;.

  2. It does not appear as though you can use a User-Defined Table Type (UDTT) as the return table type. That will need to be specified explicitly (i.e. each column name and datatype).

If this were a Scalar UDF, then the syntax of specifying only the datatype name in the RETURNS clause, and specifying the variable name in the RETURN statement, would be correct. However, you cannot return TABLE (or CURSOR) types from a Scalar UDF.

Please see the MSDN page for CREATE FUNCTION for more details.

You will need something along the lines of:

ALTER FUNCTION [dbo].[fn_CalculateListing]
(
  @Listing dbo.TypeListingDatePrice READONLY
)
RETURNS @ListingDateDetails TABLE (ColumnName DataType,...)
AS 
BEGIN
    -- Do stuffs

    RETURN;
END;
GO

For a broader discussion of the available techniques to pass sets of rows around, see: How to Share Data between Stored Procedures by Erland Sommarskog.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.