Beginning SQL Server 2005 Programming by pptfiles

VIEWS: 17 PAGES: 33

									Beginning SQL Server 2005 Programming

Chapter 12 – Stored Procedures
       


  

What Is a Stored Procedure – or “Sproc”? Creating Sprocs Altering and Dropping Sprocs Parameters Control of Flow Statements Return Values Error Handling Sproc Benefits Extended Stored Procedures Recursion Debugging .NET Assemblies

What Is a Stored Procedure?


Frequently referred to as a “sproc”


Occasionally also referred to as an “s-proc”
Callable Parameterized Removes these steps at execution, increasing performance



An ordered set of statements generally designed to encapsulate a single general task
 



Code and queries are generally precompiled and preoptimized


 

Usually written in T-SQL Can be a reference to a method in a .NET assembly (major increase in system complexity)‫‏‬

Creating Sprocs


Starts with the familiar
CREATE <object type> <object name>

  

Can have both input parameters and output parameters Utilizes the AS clause much like a view Has additional parameters to control specifics of execution

General Syntax
CREATE PROCEDURE|PROC <sproc name> [<parameter name> [schema.]<data type> [VARYING] [= <default value>] [OUT [PUT]][, <parameter name> [schema.]<data type> [VARYING] [= <default value>] [OUT[PUT]][, … … ]] [WITH RECOMPILE| ENCRYPTION | [EXECUTE AS { CALLER|SELF|OWNER|<’user name’>}] [FOR REPLICATION] AS <code> | EXTERNAL NAME <assembly name>.<assembly class>

Altering and Dropping Sprocs


Altering a sproc completely replaces the existing sproc except for permission settings


Must re-include any option settings including encryption, schema-binding, etc.

ALTER PROC <sproc name> <all other options are the same>


Dropping a sproc removes it entirely
DROP PROC <proc name>

Stored Procedures - Parameters


Most parameters will be input parameters
 

Accepts zero to many parameters Provides one strongly typed “return value” Name (same rules for naming as a variable)‫‏‬ Data type Default value (optional)‫‏‬ Direction
 

 

Also provides for output parameters 2-4 elements to a parameter
   

INPUT or OUTPUT Default is INPUT

Table Valued Functions


Results are strongly typed and can be substituted for a table in queries
  

Add to the FROM clause or use in a join Can apply WHERE clause to the result No inherent way to index

 

Can use to wrap complex query logic Provides a means to create “parametrized queries”

Sproc - Example


Simple Paramaterized sproc example
CREATE PROC spInsertShipper @CompanyName nvarchar(40), @Phone nvarchar(24)‫‏‬ AS INSERT INTO Shippers VALUES (@CompanyName, @Phone)‫‏‬



Execution example
EXEC spInsertShipper 'Speedy Shippers, Inc.', '(503) 5555566'

OUTPUT Variables






OUTPUT keyword required in the declaration for each output variable Must also use the OUTPUT keyword when you call the sproc Output value is assigned positionally or utilizing an “=” if naming your parameter list on the call


Receiving variable does not have to have the same name as the parameter

Control of Flow Statements


SQL Server provides 5 major control of flow statements
    

IF...ELSE GOTO WHILE WAITFOR TRY/CATCH



Also supports CASE statements, but is not a flow of execution statement in SQL


Utilized more as a string substitution operator

IF...ELSE


Work much as they do in most major programming languages
IF <Boolean Expression> <SQL statement> | BEGIN <code series> END [ELSE <SQL statement> | BEGIN <code series> END]







Virtually any expression that results in a boolean can be used Make sure you treat nulls properly (IS rather than =)‫‏‬ If more than a single statement depends on the IF or ELSE, must wrap in BEGIN...END block

GOTO


Again, works pretty much as it does in many major langauges
GOTO <label>



Label can be any single word followed by a colon
GOTO Destination PRINT 'Will be skipped' Destination: PRINT 'Will be executed'

WHILE


Again, works much as in other languages


Test occurs at the top of the loop (prior to first statements being executed)‫‏‬

WHILE <Boolean expression> <SQL statement> | BEGIN <code series> [BREAK] | [CONTINUE] END


BREAK


Immediately exits the loop



CONTINUE


Skips the remaining statements in this loop’s executions and immediately returns to the test condition

WAITFOR
 

Temporal control of flow Stops all execution until the time related condition is met
 

Can be a delay of a specified amount of time measured from when the WAITFOR was first executed Can be a specific time of day

WAITFOR DELAY <'time'> | TIME <'time'>

WHILE/WAITFOR Example


Run a stored procedure every night at 1 a.m.
WHILE 1 = 1 BEGIN WAITFOR TIME '01:00' EXEC sp_updatestats END

TRY/CATCH
 





New with SQL Server 2005 Yet again works much like other languages with similar keywords Statements wrapped in the TRY block are executed normally Code path immediately branches to the CATCH block in the event of an error
BEGIN TRY { <sql statement(s)> } END TRY BEGIN CATCH { <sql statement(s)> } END CATCH [ ; ]

TRY/CATCH Blocks


A number of special functions are available when in the CATCH block
   

 

ERROR_NUMBER() - Actual Error Number. ERROR_SEVERITY() - The Error Level associated with the error that caused the CATCH to be triggered. ERROR_STATE() - Equates to the error state of the error that caused the CATCH to be triggered. ERROR_PROCEDURE() - Supplies the name of the procedure that caused the error. Very useful in nested procedure/function/trigger scenarios. ERROR_LINE() - The line number of the error. ERROR_MESSAGE() - The text of the error.

Return Values


Two typical uses
 

Designed use is to return the status of the procedure when it terminated Semi-common twisting of the use is to return integer data to the calling procedure


Avoid this use!




Utilizes the RETURN keyword Immediately exits the procedure


All subsequent statements are ignored Can be passed via variable



Value returned must be an integer


Dealing with Errors


Three common types of errors in SQL Server


Run-time errors
 

Cause immediate termination of the procedure Can be trapped via TRY/CATCH in most cases, but not by earlier error handling methods

 

Warning errors
 

Do not terminate execution of the procedure Do produce an error code and level
No way for SQL Server to know that these are errors unless you detect and manually raise them

Logic errors


Don't Forget the Ways of the Past
 

TRY/CATCH is new with this release Many systems will still require backward compatibility or will have existing code

Inline Error Checking


No handling for run-time errors


Had to be handled in the client since termination was immediate in the procedure
System function that returns the number of the error of the last statement executed Must be either tested or moved in the very next statement, or the value is lost New test of @@ERROR is needed after every statement that has the potential to be a source of errors



@@ERROR
  

Error Checking Example
-- Declare our variables DECLARE @Error <some statements> SELECT @Error = @@ERROR IF @Error != 0 BEGIN -- Uh, oh—something went wrong. -- Use a GOTO to execute an error handler GOTO ErrorHandler END int

<other statements>
RETURN 0 -- this is the “normal” return

ErrorHandler: <error handler code>

Manually Raising Errors




Sometimes you want to pass your own errors to the calling routine RAISERROR


Notice only one “E”

RAISERROR (<message ID | message string>, <severity>, <state> [, <argument> [,<...n>]] )‫‏‬ [WITH option[,...n]]

 

MessageID/String – The system message ID or an actual string you want returned with the error. Severity – Indicator of how bad the error is. Can generate a warning or run-time error as well as system level errors.

Manually Raising Errors


RAISERROR (continued)‫‏‬


State
   



Ad Hoc value Will be 1 for all system generated errors Is whatever you set it to for manually generated errors Makes a nice position indicator if you have multiple places where the same error can occur Values between 1 and 127 Substitution codes available to customize some otherwise system messages Determines whether the O/S system log is written to or not as well as whether @@ERROR is properly set



Error arguments




WITH


sp_addmessage




Allows you to store your own custom messages on the system Messages are stored in the master database – not in your application's database. Plan accordingly!

Handling Errors Before They Happen






Making a parameter required does not stop the user from explicitly calling out NULL as the value Test the reasonableness of data before investing a lot of work in it Can allow you to raise more specific errors that help you guide the user to a fix

Sproc Benefits
 

Encapsulates common logic into a callable process Security
 

Hides logic Like a view, can mask how to get at underlying data


Allowing execution of a specific sproc can be granted without giving the user direct access to the underlying tables or views



Performance
  

Often precompiled


Certain situations cause a spoc to have to recompile prior to each execution

Queries are preoptimized Occasionally can recompile based on heuristics

Extended Stored Procedures




External DLL containing routines that SQL Server can call External means



Often abbreviated as “xp” in writing

  

Relatively limitless possibilities in functionality Capable of de-stabilizing your server Can created security risks

Recursion


 



Where a process, whether directly or indirectly, calls itself Is allowed up to 32 levels deep You are responsible for the recursion check to make sure it is not an infinite loop Rarely used, but when needed, nothing else will do!

Debugging
  

 



Built in step debugger in SQL Server Also supported by Visual Studio Can be difficult to get running properly – especially on a remote server Must have admin rights Very useful for quickly analyzing and debugging sprocs, functions and triggers In Management Studio, navigate to the object to be debugged. Right click, and select Step Into

.NET Assemblies



 

Augment the base functionality available in SQL Server Can replace xp's in most cases Requires .NET 2.0 3 Security models
 





SAFE: Assembly is blocked from accessing anything external to SQL Server. EXTERNAL_ACCESS: The safety and gate keeping of managed code, but allows access to external resources such as the network or file system. UNSAFE: Allows access to external resource and, more importantly, to run unmanaged code. Much higher risk of creating memory leaks or other problems that affect the stability of your server. Use of anything but SAFE requires your database to be marked as TRUSTWORTHY.

.NET Assemblies


Full assembly is loaded into SQL Server


Changes on the file system after the fact will not change the SQL Server version





One loaded assembly can support multiple stored procedures, functions, triggers, or data types Individual SQL Server objects (sprocs, etc.) that are to be .NET based are associated with specific methods in the assembly


								
To top