Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
  SQL Server Tips by Gama and Naughter

 

The DEFAULT keyword


When using parameters passed by location, omitting a default parameter will only work if this parameter is the last one in the parameter list. The only way to specify that a parameter must get its default value without skipping the parameter is to use the DEFAULT keyword. DEFAULT tells the SP to get this parameter’s default value. This keyword will only work if the parameter has a default value defined in the SP, otherwise it will cause an error.


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.


CREATE PROCEDURE cap4 (@name NVARCHAR(20) OUT, @2ndname NVARCHAR(20) =NULL)
--Capitalizes first char, changes the rest to lower case
--SP with one input/output parameter and an optional input parameter
AS
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.


CREATE PROCEDURE cap5 (@name1 NVARCHAR(20) =NULL OUT, @name2 NVARCHAR(20) =NULL OUT, @name3 NVARCHAR(20)
=NULL OUT )
--Capitalizes first char, changes the rest to lower case
--SP with three optional input/output parameters
AS
IF NOT @name1 IS NULL
SET @name1=UPPER(LEFT(@name1,1))+LOWER(RIGHT(@name1,LEN(@name1)-1))
IF NOT @name2 IS NULL
SET @name2=UPPER(LEFT(@name2,1))+LOWER(RIGHT(@name2,LEN(@name2)-1))
IF NOT @name3 IS NULL
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

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

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