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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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