dual purpose OUTPUT parameter

Question

dual purpose OUTPUT parameter

dgl

SSC Veteran

Points: 229
More actions
October 21, 2014 at 7:54 am

#291186

Assume I built a stored proc (dbo.testproc) that will return the OUTPUT parameter @RandomInteger.
I could pass a specific value for the parameter...
EXEC dbo.testproc 7
Or I could return a value from the proc...
DECLARE @ReturnInteger
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger
But I want to do both which, if this actually worked, might look like this...
DECLARE @RandomInteger
SET @RandomInteger = 7
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger
I want to pass a specific value and return the result from the proc. Do I need to use two parameters for this?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dgl SSC Veteran Points: 229 More actions · Answer 1

If it's of any interest, here is the vague scenario that fits my original question.

Normally, this proc has two possibilities. 1) The parameter is known so we pass it to the proc. We don't need to return the value because we already know it. 2) The parameter is not known so it is not passed to the proc. We need to return the value because we don't know it.

However, a third case has come up. We may know the value of the parameter (i.e. option 1 above) but the proc may identify an issue with the data which requires the input proc to change the parameter value in which case we want that value returned (i.e. option 2 above).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Rather use two parameters. It's one of those good coding practices, a parameter should be one thing only.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dgl SSC Veteran Points: 229 More actions · Answer 3

Fair enough, Gila Monster.

FWIW, I found this...

http://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

"Input values can also be specified for OUTPUT parameters when the stored procedure is executed. This allows the stored procedure to receive a value from the calling program, change it or perform operations with it, then return the new value to the calling program."

As I read that section, it appears that I should be able to do what I want to do but I don't understand how.

dgl SSC Veteran Points: 229 More actions · Answer 4

Hooray! I figured it out.

The thing is to set the OUTPUT parameter as noted below, which I was doing backwards.

DECLARE @ReturnInteger

SET @ReturnInteger = 7

EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT

SELECT @ReturnInteger

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 5

I'm with Gail. Two parameters. At some point you may refactor this and you'll want the separation.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Wouldn't it be easier and more natural to refactor this as a function?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 7

ChrisM@Work (10/21/2014)
Wouldn't it be easier and more natural to refactor this as a function?

I agree but to return a random number, you'd probably want to use a GUID as the random seed. I'm pretty sure than NEWID() still isn't allowed in a function in 2012 but you could always use the "VIEW" trick to get a GUID.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)