SQL Scalar Function to call stored procedure with output parameter

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].[uspUserHasPermission] @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 
	@p_uID varchar(8), 
	@p_permit varchar(64)	
RETURNS int	--, @p_authPermit int OUTPUT
	DECLARE @p_authPermit as int

	EXECUTE [uspUserHasPermission] @p_uID, @p_permit, @p_authPermit OUTPUT

	RETURN @p_authPermit


Open in new window

...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?
LVL 11
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
actually, if both the procedure and the function are yours to be modified, yes that works

I thought the procedure called was a system proc or some proc that shall not be modified.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>...which I interpret to mean that a scalar function cannot call a stored procedure with a return value the way I have it.

and I know no alternative so far ;(
kbireckiAuthor Commented:
I just thought of switching them around.  I could move the meat of the usp to the scalar and call the scalar from the usp.  Maybe that is the best option, huh?
kbireckiAuthor Commented:
Excellent.  Thank you!
kbireckiAuthor Commented:
Thanks for the follow-up.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.