💠🛠️🗒️SQL Server: Stored procedures (Notes)

Disclaimer: This is work in progress based on notes gathered over the years, intended to consolidate information from the various sources. The content needs yet to be reviewed against the current documentation.

Last updated: 19-Mar-2024

[SQL Server 2005] Stored procedure

• {def} a database object that encapsulates one or more statements and compiled when used
• is a saved batch
• whatever a batch can do, a stored procedure can do
• {characteristic} abstraction layer
• provides the means of abstracting/decoupling a database [1]
• {characteristic} performant
•  the fastest possible code, when well-written
• keeps the execution of data-centric code close to the data
• easier to index tune a database with stored procedures
• {characteristics} usability
• easier to write, consume and troubleshoot stored procedures
• less likely to contain data integrity errors
• easier to unit test, than ad-hoc SQL code
• {characteristic} secure
• {best practice} locking down the tables and providing access only through stored procedures is a standard for database development [1]
• minimizes the risk of sql injection attacks
• provides an alternative for passing a dataset to SQL Server
• can have zero or more input parameters
• can have zero or more output parameters
• highly dependent on the objects it calls
• [SQL Server 2008] provides enhanced ways to view these dependencies
• managed by means of the DDL commands
• {operation} create stored procedure
• via CREATE STORED PROCEDURE <schema.name> 
• CREATE must be the first command in a batch;
• the termination of the batch ends the creation of the stored procedure
• when created, its text is saved in a system table
• like other database objects
• the text is only stored as definition 
• ⇐  the text is not stored for the execution of the stored procedure
• {best practice}
• never use "sp_" to prefix the name of a stored procedure
• reserved for system stored procedures
• {best practice} use a standard prefix for the stored procedure name (e.g. usp, Proc)
• it helps identify an object as a stored procedure when reviewing and troubleshooting code [15]
• {best practice} always use a two-part naming convention
• ensures that the stored procedure is added to the appropriate schema [15]
• {best practice} use descriptive names
• {best practice} implement error handling
• syntax and logic errors should be gracefully handled, with meaningful information sent back to the calling application [15]
• {operation} alter stored procedure
• replaces the entire existing stored procedure with new code
• preferable to dropping and recreating it
• because the latter method removes any permissions [1]
• {operation} drop stored procedure
•  removes it from the database
• {operation} execute stored procedure
• via EXECUTE or EXEC
• can be executed individually also without EXECUTE when the stored procedure is the first line of a batch
• {concept}  compilation
• automatic process that takes place the first time the code is executed 
• {option} WITH ENCRYPTION
• obfuscates the code in the object 
• is not to prevent a user from reading the code 
• the stored procedure text is not directly readable
• there is no routine to hide the code
• SQL Server applies a bitwise OR to the code in the object
• anyone with VIEW DEFINITION authority on an object can see the code though
• carried from early versions of SQL Server [2]
• {best practice} there must be a compelling and carefully considered justification for encrypting a stored procedure [15]
• e.g.  third-party software
• {type} system stored procedures
• stored in master database
• {type} extended stored procedures
• routines residing in DLLs that function similarly to regular stored procedures [33]
• usually written in C or C++
• receive parameters and return results via SQL Server's Open Data Services API [33]
• reside in the master database [33]
• run within the SQL Server process space [33]
• aren't automatically located in the master database [33]
• don't assume the context of the current database when executed [33]
• fully qualify the reference to execute an extended procedure from a database other than the master [33]
• {workaround} wrapping the extended stored procedure into a system stored procedure
•  can be called from any database without requiring the master prefix
• technique used with a number of SQL Server's own extended procedures
• {type} internal stored procedures 
• system-supplied stored procedures implemented internally by SQL Server [33]
• have stubs in master..sysobjects
• are neither true system procedures nor extended procedures [33]
• listed as extended procedures, but they are actually implemented internally by the server [33]
• cannot be dropped or replaced with updated DLLs
• normally this happens when a service pack is applied [33]
• examples: 
• sp_executesql
• sp_xml_preparedocument
• most of the sp_cursor routines
• sp_reset_connection
• {type} user-defined stored procedures
• {type} remote stored procedures
• may only be remotely called
• ⇐  may not be remotely created [15]
• require that the remote server be a linked server
• namely, a four-part name reference 
• via EXECUTE Server.Database.Schema.StoredProcedureName;
• a distributed query
• OpenQuery(LinkedServerName, 'EXECUTE Schema.StoredProcedureName');
• {type} recursive stored procedures 
• stored procedures that call themselves 
• perform numeric computations that lend themselves to repetitive evaluation by the same processing steps [33]
• calls can be nested up to 32 levels deep
• {type} nested stored procedure
• calls can be nested up to 32 levels deep
• {advantage} execution plan retention and reuse
• {advantage} query auto-parameterization
• {advantage} allow encapsulation of business rules and policies
• {advantage} allow application modularization
• {advantage} allow sharing of application logic between applications
• {advantage} allow access to database objects that is both secure and uniform
• {advantage} allow consistent, safe data modification
• {advantage} allow network bandwidth conservation
• {advantage} support for automatic execution at system start-up
• {limitation} cannot be schema bound
• [SQL Server 2012] {feature} Result Sets 
• can guarantee the structure of the returned results at run time
• {myth} a stored procedure provide a performance benefit because the execution plan is cached and stored for reuse
• [SQL Server 2000] all execution plans are cached, regardless of whether they’re the result of inline T-SQL or a stored procedure call
• {corollary} all T-SQL must be encapsulated into stored procedures
• {myth} stored procedures are hard to manage

Previous Post <<||>> Next Post

References:
[1] Paul Nielsen et al (2009 SQL Server 2008 Bible
[2] Tobias Thernström et al (2009) MCTS Exam 70-433 Microsoft SQL Server 2008 – Database Development. Self-Paced Training Kit
[7] Michael Lee & Gentry Bieker (2008) Mastering SQL Server® 2008
[8] Joseph Sack (2008) SQL Server 2008 Transact-SQL Recipes
[15] Adam Jorgensen et al (2012)  Microsoft® SQL Server® 2012 Bible
[26] Patrick LeBlanc (2013) Microsoft SQL Server 2012: Step by Step, Microsoft Press
[33] Ken Henderson (2001) The Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML
[42] Dušan Petkovic (2008) Microsoft® SQL Server™ 2008: A Beginner’s Guide
[51] Michael Lee & Gentry Bieker (2009) Mastering SQL Server® 2008
[64] Robert D Schneider and Darril Gibson (2008) Microsoft® SQL Server® 2008 All-In-One Desk Reference for Dummies
[100] Itzik Ben-Gan et al (2012) Exam 70-461: Querying Microsoft SQL Server 2012

Acronyms:
DDL - Data Definition Language
DLL - Dynamic Link Library