[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Scalar Function to call stored procedure with output parameter

Posted on 2011-10-12
5
Medium Priority
?
892 Views
Last Modified: 2012-06-27
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
AS
BEGIN
	DECLARE @p_authPermit as int

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

	RETURN @p_authPermit

END

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?
Thanks!
0
Comment
Question by:kbirecki
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36954993
>...which I interpret to mean that a scalar function cannot call a stored procedure with a return value the way I have it.
exactly

and I know no alternative so far ;(
0
 
LVL 11

Author Comment

by:kbirecki
ID: 36955031
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?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 36955113
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.
0
 
LVL 11

Author Comment

by:kbirecki
ID: 36955128
Excellent.  Thank you!
0
 
LVL 11

Author Closing Comment

by:kbirecki
ID: 36955129
Thanks for the follow-up.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question