Sql stored procedure output parameter default value

SQL Server Tips by Gama and Naughter.

sql server - Initialize output parameters in stored procedure - Stack Overflow

SP with optional Input parameters Setting a default with a NULL value simulates optional parameters. Then the code will discard it because it assumes NULL as meaning optional parameter omitted, therefore unused. Transferring the output of a SP to a table If a table needs the output from a SP that returns several rows of output then the INSERT EXEC statement is the perfect choice.

It is analogous to INSERT SELECT: Join between a table and the output of a SP This is an example with the sole purpose of proving that it is possible to join the output from a SP with a table. This output, by using OpenRowset, works like a table or view being sorted, filtered, etc.

Sql Server output parameters - Database Administrators Stack Exchange

The negative aspect is that OpenRowset opens a new connection to the server as if it was a remote server because that is its functionality. This will result in terrible performance due to the added overhead.

Applying a cursor to the output of a SP The best solution is to use a temporary table but OpenRowSet will do the job as well.

This is the perfect example to demonstrate how OpenRowSet affects performance. This can be a hard task when there is no real world data for testing, the data is scarce or its changes unpredictable, the data structure might change, the number of users is unknown, index columns might change, etc.

Specifying a Parameter Default Value

This is what makes database design and development an art. The examples for this section use the Pubs database. Burleson is the America's Team Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.

If you find it confusing, please exit this page. SQL Server technology is changing and we strive to update our SQL Server support information.

If you find an error or have a suggestion for improving our content, we would appreciate your feedback. The above book excerpt is from: Burleson is the America's Team. Burleson Consulting SQL Server database support.