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

 

Start up SP's
 

Starts up SPís are executed when SQL Server is started. They are user SP's created in the master database and then having the ExecIsStartup property set. This property can be changed directly from Enterprise Manager by opening the property pane of the SP or with the system SP sp_makestartup.
 

Each start up SP runs in a separate connection simultaneously, therefore, if more than one start up SP is necessary there are two possibilities:
 

If it not acceptable to have more than one connection for the start up SP's or they must follow a sequential execution then it would be better to have only one start up SP which would call the others.
 

Otherwise, it is ok to have several start up SP's running in parallel, in different connections.

 

These SP's can be useful for monitoring, management, maintenance and security purposes.

 

Example: Create a start up SP that will check and repair the allocation and structural integrity of the pubs' database.

 

This is the SP:

 

USE master

CREATE PROCEDURE sp_autoexec

--Check and repair pubs

AS

DBCC CHECKDB ('pubs', REPAIR_REBUILD )

 

To turn it into a start up SP:

 

EXEC sp_makestartup sp_autoexec

 

There is no direct way to list all start up procedures, but the following SP can be created for that purpose:

 

CREATE PROCEDURE sp_helpstartup

--List all start up procedures

AS

SELECT name FROM master..sysobjects

WHERE xtype = 'p'

AND objectproperty(id, 'ExecIsStartup') = 1

 

Recursive SP's
 

A SP can call itself with up to 32 levels of recursion. If there is no control variable to stop once the maximum level of recursion is reached, the SP will generate an error and cease execution. This is very problematic in situations where a transaction would rollback because of the error. This could result in data inconsistency that would lead either to the business process to be interrupted or with incorrect output.

 

The most common example of a recursive function is the factorial function that returns, for an integer input, the product of the input by each integer below it. This is an SP that calculates the factorial of an integer:

 

CREATE PROCEDURE spFactorial (@intInput decimal(38,0), @fact decimal(38,0) OUT)

--SP for calculating the factorial recursively

AS

DECLARE @tmp decimal(38,0) --declare temporary variable

IF (@intInput>31) OR (@intInput<0) --if the input is negative or higher than the maximum allowed

      SET @fact=0 --return zero

ELSE

      IF @intInput=0 --the output for both 0 and 1 is 1

               SET @fact=1 --fact(0)=fact(1)=1

      ELSE

               BEGIN

               SET @fact=@intInput --store input value

               SET @intInput=@intInput-1 --decrease input

               EXEC spFactorial @intInput, @tmp OUT --recursive call

               SET @fact=@fact*@tmp --fact(n)=n*fact(n-1)

               END

 

The factorial of zero is one, by definition and the factorial of a negative number is an undefined. Every recursive function needs a stop condition and it usually is when the input reaches zero, which would return one. The recursive calls would stop at that point and the consecutive calls to the function, on the stack, would start returning their values to their precedent callers.

 

Calling the SP:

 

DECLARE @a decimal(38,0)

EXEC spFactorial 31, @a out

SELECT @a

 

Output:

8222838654177922817725562880000000

 

If the SP didn't have the condition to stop when reaching 31 levels of recursion, it would cause an error:
 

Server: Msg 217, Level 16, State 1, Procedure spFactorial, Line 15

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

 

Advantages of recursive SP's:

 

Using simple and elegant algorithms.

Traversing data from a table without a scroll cursor.

 

Disadvantages of recursive SP's:

High overhead.

Limited to 32 levels of recursion.

Difficult error management.


 


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