« Refuting Evolution by Jonathan Sarfati, Part IBill 44 Passes Second Reading »

Passing By Reference To SQL Server Stored Procedures

06/15/09

  12:14:11 pm, by Nimble   , 151 words  
Categories: Programming

Passing By Reference To SQL Server Stored Procedures

This is pretty simple to accomplish, though how exactly to do it can be hard to find.

Quite simply, you add OUTPUT to your parameter lists, on both your procedure definition and your actual call, like so:

Defining:

CREATE PROCEDURE dbo.TestValue
@Value int OUTPUT
AS
SET @Value = @Value + 1
GO

Calling:

DECLARE @Banana int
SET @Banana = 1
PRINT @Banana
EXEC dbo.TestValue @Banana OUTPUT
PRINT @Banana
EXEC dbo.TestValue @Banana OUTPUT
PRINT @Banana
GO

If you run this in SQL Server Management Studio, this will give you the output:

1
2
3

If you miss the OUTPUT on the calling side, your value will simply be unaffected, and will output instead:

1
1
1

If you miss the OUTPUT on the defining side, the OUTPUT on the calling side will generate an error:

Msg 8162, Level 16, State 2, Procedure TestValue, Line 0
The formal parameter "@Value" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

No feedback yet

March 2019
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            
Ritchie Annand's blog of everything except sports (...and who knows? I may break that rule some day)

Search

  XML Feeds

blogging tool