Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

SQL Server 2008 R2 : Creating and Managing Stored Procedures - Using Output Parameters, Returning Procedure Status

4/17/2013 3:58:30 PM

1. Using Output Parameters

If a calling batch passes a variable as a parameter to a stored procedure and that parameter is modified inside the procedure, the modifications are not passed to the calling batch unless you specify the OUTPUT keyword for the parameter when executing the stored procedure.

If you want a procedure to be able to pass parameter values out from the procedure, you need to use the keyword OUTPUT when creating the procedure. The following example shows a stored procedure that accepts two parameters, one of which is used as an output parameter:

IF EXISTS ( SELECT * FROM sys.procedures
               WHERE schema_id = schema_id('dbo')
                 AND name = N'ytd_sales')
   DROP PROCEDURE dbo.ytd_sales
GO
CREATE PROC ytd_sales
@title varchar(80), @ytd_sales int OUTPUT
AS
SELECT @ytd_sales = ytd_sales
   FROM titles
   WHERE title = @title
RETURN

The calling batch (or stored procedure) needs to declare a variable to store the returned value. The execute statement must include the OUTPUT keyword as well, or the modifications won’t be reflected in the calling batch’s variable:

DECLARE @sales_up_to_today  int
EXEC ytd_sales 'Life Without Fear', @sales_up_to_today OUTPUT
PRINT 'Sales this year until today''s date: ' +
      CONVERT(VARCHAR(10), @sales_up_to_today) + '.'

Sales this year until today's date: 111.

You can also pass the output parameter by name:

DECLARE @sales_up_to_today  int
EXEC ytd_sales 'Life Without Fear',
      @ytd_sales = @sales_up_to_today OUTPUT
PRINT 'Sales this year until today''s date: ' +
      CONVERT(VARCHAR(10), @sales_up_to_today) + '.'

Note that when you pass an output parameter by name, the parameter name (@ytd_sales, in this example) is listed on the left side of the expression, and the local variable (@sales_up_to_today), which is set equal to the value of the output parameter, is on the right side of the expression. An output parameter can also serve as an input parameter.

Output parameters can also be passed back and captured in a client application by using ADO, ODBC, OLE DB, and so on.

2. Returning Procedure Status

Most programming languages are able to pass a status code to the caller of a function or subroutine. A value of 0 generally indicates that the execution was successful. SQL Server stored procedures are no exception.

SQL Server automatically generates an integer status value of 0 after successful completion of a stored procedure. If SQL Server detects a system error, a status value between -1 and -99 is returned. You can use the RETURN statement to explicitly pass a status value less than -99 or greater than 0. The calling batch or procedure can set up a local variable to retrieve and check the return status.

In Listing 1, the stored procedure returns the year-to-date sales for a given title as a result set. If the title does not exist, to avoid returning an empty result set, the procedure returns the status value -101. In the calling batch or stored procedure, you need to create a variable to hold the return value. The variable name is passed the EXECUTE keyword and the procedure name as shown in Listing 28.16.

Listing 1. Returning a Status Code from a Stored Procedure
IF EXISTS ( SELECT * FROM sys.procedures
               WHERE schema_id = schema_id('dbo')
                 AND name = N'ytd_sales2')
   DROP PROCEDURE dbo.ytd_sales2
GO
--Create the procedure
CREATE PROC ytd_sales2 @title varchar(80)
AS
IF NOT EXISTS (SELECT * FROM titles WHERE title = @title)
    RETURN -101
SELECT ytd_sales
   FROM titles
   WHERE title = @title
RETURN
GO

-- Execute the procedure
DECLARE @status int
EXEC @status = ytd_sales2 'Life without Fear'
IF @status = -101
    PRINT 'No title with that name found.'
go

ytd_sales
---------
111
-- Execute the procedure
DECLARE @status int
EXEC @status = ytd_sales2 'Life without Beer'
IF @status = -101
    PRINT 'No title with that name found.'
go

 No title with that name found.


					  

Return values can also be passed back and captured by client applications developed in ADO, ODBC, OLE DB, and so on.

Other -----------------
- Windows Server 2008 : Designing the Active Directory Administrative Model (part 3) - Planning to Audit AD DS and Group Policy Compliance, Planning Organizational Structure
- Windows Server 2008 : Designing the Active Directory Administrative Model (part 2) - Using Group Strategy to Delegate Management Tasks
- Windows Server 2008 : Designing the Active Directory Administrative Model (part 1) - Delegating Active Directory Administration
- BizTalk Server 2006 : Starting a New BizTalk Project - Organizing Artifacts in BizTalk 2006
- BizTalk Server 2006 : Starting a New BizTalk Project - Structuring and Integrating with Visual Studio
- Deploying the Client for Microsoft Exchange Server 2007 : Planning Considerations and Best Practices, Preparing the Deployment
- Deploying the Client for Microsoft Exchange Server 2007 : Outlook 2007 Auto Account Setup, Understanding Deployment Options
- Microsoft Systems Management Server 2003 : Creating Packages for Distribution (part 6) - Package Distribution Process Flow
- Microsoft Systems Management Server 2003 : Creating Packages for Distribution (part 5) - Creating a Package from a Definition File
- Microsoft Systems Management Server 2003 : Creating Packages for Distribution (part 4) - Creating a Package from Scratch - Creating Programs
- Microsoft Systems Management Server 2003 : Creating Packages for Distribution (part 3) - Creating a Package from Scratch - Defining Distribution Points
- Microsoft Systems Management Server 2003 : Creating Packages for Distribution (part 2) - Creating a Package from Scratch - Defining Access Accounts
- Microsoft Systems Management Server 2003 : Creating Packages for Distribution (part 1) - Creating a Package from Scratch
- Microsoft Systems Management Server 2003 : Running Software Metering Reports
- Microsoft Dynamics GP 2010 : Improving performance by adjusting AutoComplete settings, Cleaning up Accounts Receivable with Paid Transaction Removal
- Microsoft Dynamics GP 2010 : Maintaining Dynamics GP - Preventing entry of wrong dates by Closing Periods
- Windows Server 2008 R2 : Creating and Administering Hyper-V Virtual Machines (part 2) - Installing the guest operating system
- Windows Server 2008 R2 : Creating and Administering Hyper-V Virtual Machines (part 1) - Virtual machine disk types
- Backup and Restore of Microsoft Lync Server 2010 : Backup Processes (part 2) - Backing Up the Central Management Store, Backing Up Lync Server Servers
- Backup and Restore of Microsoft Lync Server 2010 : Backup Processes (part 1) - Backing Up Lync Server Databases
 
 
Most view of day
- Microsoft Project 2010 : Tracking Progress on Tasks (part 3) - Entering a Task’s Completion Percentage
- SharePoint 2010 : Configuring Search Settings and the User Interface - The Preferences Page: An Administrator's View
- BizTalk Server 2009 Operations : Disaster Recovery (part 2)
- SQL Server 2012 : Latch Contention Examples - Inserts When the Clustered Index Key Is an Identity Field
- Maintaining Security : Restricting Content in Windows Media Center, Creating Trusted Contacts
- Designing an Update Management Strategy : Configuring an Update Testing Infrastructure, Verifying Update Deployment
- Microsoft PowerPoint 2010 : Inserting Content from External Sources - Inserting New Slides from an Outline
- Installing and Configuring the Basics of Exchange Server 2013 for a Brand-New Environment (part 4)
- Extending Dynamics GP with Free Software : Preventing date errors with DocDateVerify, Executing SQL from the Support Administrator Console
- Managing Change through Group Policy (part 1) - Working with Local Policies
Top 10
- Windows Phone 8 : Scheduled Tasks - Scheduled Task API Limitations
- Windows Phone 8 : Scheduled Tasks - Updating Tiles Using a Scheduled Task Agent
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 5) - Editing an Existing To-Do Item
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 4) - Creating the To-Do Item Shell Tile, Saving a To-Do Item
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 3) - Debugging Scheduled Tasks
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 2) - TodoService, TodoItemViewModel
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 1) - TodoItem,TodoDataContext
- Windows Phone 8 : Scheduled Tasks - Using Scheduled Tasks
- Windows Phone 8 : Scheduled Tasks - Background Agent Types
- Windows Phone 8 : Windows Phone Toolkit Animated Page Transitions - Reusing the Transition Attached Properties
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro