0

I want to pass a table variable to a fuction (which would contains different value at different time) as input, returning a calculated result in the form of another table, which would be declared in the function.

Something like this:

CREATE FUNCTION fn_CalculateListing(@ListingData TABLE(ROWID INT,value1 INT, value2 INT))
RETURNS TABLE
AS
BEGIN
    DECLARE @result TABLE
    (
        result1 INT,
        result2 INT
    )

    INSERT INTO @result(result1,result2)
    SELECT value1*value2, value1/value2
    FROM ListingDate

    RETURN @result
END

But the above syntax give me an error near declaration @ListingDate TABLE as incorrect syntax.

Is above syntax correct, or should I use another one?

Daniel Hutmacher
  • 8,810
  • 1
  • 19
  • 51
Kaishu
  • 517
  • 1
  • 10
  • 18

1 Answers1

3

You cannot include a table declaration in the stored procedure declaration. If you really want to pass rows of data, create a table-valued parameter type, then use it in your stored procedure.

How about a simpler table valued function, that calculates product and quotient of 2 integers:

CREATE FUNCTION fn_CalcProductQuotient(
@Value1 INT,
@Value2 INT)
RETURNS TABLE
AS
RETURN (
SELECT @Value1 * @Value2 AS [Product]
       ,@Value1 / @Value2 AS [Quotient]
);

Make sure to use an inline user-defined function. This way the plan optimiser can parallelise your plan (if you have multiple cores) and you will get much better performance.

You can use this function as following:

SELECT  [RowID]
       ,[Value1]
       ,[Value2]
       ,Calc.[Product]
       ,Calc.[Quotient]
FROM Items i
CROSS APPLY fn_CalcProductQuotient(Value1, Value2) Calc
Serge
  • 542
  • 3
  • 9