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?
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)