Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Gama and Naughter


SP with optional Input parameters

Setting a default with a NULL value simulates optional parameters. By inspecting the parameter’s value it is then determined that it is optional if its value is NULL.

--Capitalizes first char, changes the rest to lower case
--SP with one input/output parameter and an optional input parameter
SET @name=UPPER(LEFT(@name,1))+LOWER(RIGHT(@name,LEN(@name)-1))
IF NOT @2ndname IS NULL
SET @name=@name+' '+UPPER(LEFT(@2ndname,1))+LOWER(RIGHT(@2ndname,LEN(@2ndname)-1))

When omitting the second parameter, it will get the default value of NULL. Then the code will discard it because it assumes NULL as meaning optional parameter omitted, therefore unused.

SP with optional Input/Output parameters

Output parameters can have default values and so they can act as optional parameters as well.

--Capitalizes first char, changes the rest to lower case
--SP with three optional input/output parameters
SET @name1=UPPER(LEFT(@name1,1))+LOWER(RIGHT(@name1,LEN(@name1)-1))
SET @name2=UPPER(LEFT(@name2,1))+LOWER(RIGHT(@name2,LEN(@name2)-1))
SET @name3=UPPER(LEFT(@name3,1))+LOWER(RIGHT(@name3,LEN(@name3)-1))

The next examples will use the SP helloworld3 that returns three records.

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:

However, INSERT EXEC does not work with variables of type “table”. Trying this technique on such tables will result in the error “EXECUTE cannot be used as a source when inserting into a table variable.”

The only solution is by means of OpenRowSet (opens a new connection though OLE DB) but the downside is the huge performance loss.

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. 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.

Mean, Median and Modal with SP’s

Sometimes the choice of an algorithm depends on the data source, number of rows, complexity of relationships or joins used in a query, complexity of calculated columns, data type peculiarities, etc. 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. This is what makes database design and development an art. The examples for this section use the Pubs database.

The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter


Burleson Consulting Remote DB Administration







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.

Errata?  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.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter