Simple Stored Procedure with Output Parameter

While working on new project at work this week, I have created a set of five stored procedures to accomplish various tasks. I thought I would share a quick example of a stored procedure that returned a success flag.

create procedure sp_procedure1
@id int,
@success int OUTPUT
AS

update table1 set col1 = ‘New Value’ where id = @id
IF @@ROWCOUNT = 0
SELECT @success = 0
ELSE
SELECT @success = 1
GO

Then to call the stored procedure you would do something like this

DECLARE @success int
exec sp_procedure1 1, @success OUTPUT
select @success

This call will execute your stored procedure. If a row is updated then you will receive a success flag of 1 back from the sp. If zero rows are updated then you will receive a success flag of 0 back from the sp.

Before working with the ouptput parameters extensivley this week, I found myself constantly hitting BOL to look this concept up. Now I can just hit this awesome blog instead if I need a quick refresher, assuming I remember to look at this blog….

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s