||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
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
Example: Create a start up SP that will check and repair the
allocation and structural integrity of the pubs' database.
This is the SP:
CREATE PROCEDURE sp_autoexec
--Check and repair pubs
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
SELECT name FROM master..sysobjects
WHERE xtype = 'p'
AND objectproperty(id, 'ExecIsStartup') = 1
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
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
--SP for calculating the factorial recursively
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
IF @intInput=0 --the output for both 0 and 1 is 1
SET @fact=1 --fact(0)=fact(1)=1
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)
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
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:
Limited to 32 levels of recursion.
Difficult error management.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter