Besides using input parameters, stored procedures can also return OUTPUT parameters and return values. Output parameters behave similarly to input parameters, but have to be declared with the OUTPUT keyword. In addition, you should specify the OUTPUT keyword when executing a stored procedure containing the output parameter to get the value. The following procedure contains an input parameter of the title type and an output parameter of total quantity of titles sold for the specified type:.

Notice that in order to use the output parameter, we have to declare a variable with the same data type as the output parameter of the called stored procedure.

We can easily extend the same procedure to return more than one output parameters:. You can execute the stored procedure with an output parameter without the OUTPUT keyword, but you won't be able to use the returned value in the calling program.

Return values can be used within stored procedures to provide the stored procedure execution status to the calling program. The return values through 0 are reserved for SQL Server internal use.

You can create your own parameters that can be passed back to the calling program. By default, the successful execution of a stored procedure or any group of SQL statements will return 0. The syntax of the return command is:. You can optionally enclose the integer value in parenthesis.

If you don't supply the integer value, SQL Server will provide a value for you, depending on the state of program execution.

RETURN also unconditionally exits the program, so once a RETURN is encountered in your T-SQL code SQL Server will not check any other conditions. The following example demonstrates usage of user-defined return codes; notice that even though multiple conditions are examined within the procedure, a single RETURN will cause the program to stop and return the appropriate value:. Now, we can execute the procedure with intentionally wrong values to check how the RETURN statement works:.

TRAINING Product Courseware Academic Program Training Courses. IBM DB2 MySQL NoSQL Oracle PostgreSQL SQL Server.

Creating and Managing Stored Procedure in SQL Server

Benchmark Factory Code Tester for Oracle SharePlex SQL Navigator SQL Optimizer Spotlight Stat Toad Intelligence Central Toad Data Modeler. Toad Data Point Toad Edge New Toad Extension for Eclipse Toad for Hadoop Toad for IBM DB2 Toad for Oracle Toad for MySQL Toad for SQL Server Toad for SAP Solutions Toad MAC Edition. Toad for Oracle Toad Edge New Toad for SQL Server Toad for IBM DB2 Toad for SAP Solutions Toad for MySQL Toad Extension for Eclipse Toad for Hadoop Toad Data Modeler Toad Data Point.

tsql - How can I use optional parameters in a T-SQL stored procedure? - Stack Overflow

Toad MAC Edition Toad Intelligence Central Benchmark Factory Code Tester for Oracle SharePlex SQL Navigator SQL Optimizer Spotlight Stat. SQL Server Community Join. Contents SQL Server Wiki SQL Server.

SQL Server Book Reviews. SQL Server DBA Training.

How to create a SQL Server stored procedure with parameters

Bulk Copy Program BCP. Data Transformation Services DTS. SQL Server Integration Services Section.

Testing and Debugging Transact SQL. Transact SQL Code Library. Transact SQL Coding Techniques. Functions - IDENTITY, SCOPE IDENTITY, IDENT CURRENT.

Recommended Best Practices Reusable Coding Techniques. Stored Procedures - Auto Executing. Stored Procedures - Creating. Stored Procedures - Examples. Stored Procedures - Parameters. Stored Procedures - Performance Issues. Stored Procedures - Using with Transactions. The following procedure contains an input parameter of the title type and an output parameter of total quantity of titles sold for the specified type: We can easily extend the same procedure to return more than one output parameters: The syntax of the return command is: The following example demonstrates usage of user-defined return codes; notice that even though multiple conditions are examined within the procedure, a single RETURN will cause the program to stop and return the appropriate value: