Returns two values ​​from a scalar SQL function

advertisements

I have a Scalar SQL function thats returns a decimal value, and this function is used in many stored procedures across my database. Now in some procedures, I need to set a value based on some criteria inside the function. To make it clearer, depending on some variables used in calculating the result of the function, I want to set another variable inside the Stored procedure, and return it to the client.

I don't want to change how the result is returned or the return type of the function. I am thinking of doing it by inserting the new value i want into an sql table and then reading it from the procedure, But is there another or better way to do it?

Thanks


You have a couple of options

1) Change it from a function to a stored procedure, and add an output parameter.

2) Change it from a scalar function to a table valued function returning a single row, with the additional value as an additional column.

If you need to preserve the existing function signature then just create a new table valued function that does the work (As per option 2 above), and modify your existing function to select from the new table valued function.

Here is some example code demonstrating this:

-- the original scalar function
CREATE FUNCTION dbo.t1(@param1 INT)
RETURNS INT AS
BEGIN
    RETURN @param1 + 1
END
GO

-- a new table valued function, that returns 2 values in a single row
CREATE FUNCTION dbo.t2(@param1 INT)
RETURNS TABLE AS
    RETURN (SELECT @param1 + 1 AS [r1], @param1 + 2 AS [r2])
GO

-- the modified original function, now selecting from the new table valued function
CREATE FUNCTION dbo.t3(@param1 INT)
RETURNS INT AS
BEGIN
    RETURN (SELECT r1 FROM dbo.t2(@param1))
END
GO

-- example usage
SELECT dbo.t1(1)
SELECT * FROM dbo.t2(1)
SELECT dbo.t3(1)