I have a SQL Server 2005 backend with some existing stored procedures that work well. One I'm dealing with has a return value and is defined like this:
ALTER PROCEDURE [dbo].[uspUserHasPermissio
n] @p_uID varchar(8), @p_permit varchar(64), @p_authPermit int OUTPUT AS
I need this to remain as it is because it is used throughout the application in this manner, but I now need an additional use of it to perform as a scalar function to be used in a query so that I don't have to have two versions of the same functional code to maintain. I just want to create a scalar function that calls [uspUserHasPermission] and returns the output from that usp. So I created the following:
CREATE FUNCTION fnUserHasPermission
RETURNS int --, @p_authPermit int OUTPUT
DECLARE @p_authPermit as int
EXECUTE [uspUserHasPermission] @p_uID, @p_permit, @p_authPermit OUTPUT
...and when I try to execute it, I get the response:
Msg 557, Level 16, State 2, Line 1
Only functions and extended stored procedures can be executed from within a function.
...which I interpret to mean that a scalar function cannot call a stored procedure with a return value the way I have it.
Is this correct? Are there any suggestions on how I can easily call a stored proc that returns a scalar value without having to repeat the complete usp code?