I was recently asked a basic question about the use of stored procedures by a developer and it got me wondering how many other developers were wondering the same thing. This post will cover the “basics” of stored procedures in SQL Server; what, why, how, etc. It’s basic knowledge, but knowledge that every developer should have in their cap. Note that most of the information here is a compiled list from many different sources and most of it is not original! Many thanks to all of the ultra-smart folks out there in cyberspace who willingly share their knowledge. Oh, and this isn’t a discourse on the stored procedure vs. T-SQL arguments; that’s a whole other topic…
Let’s start with a definition; what is a stored procedure? Simply put a stored procedure is a set of one or more T-SQL statements that are stored together as a unit (a single object) in the SQL Server system tables. This set of statements can be executed by a simple call to the procedure by name.
Why use stored procedures? I’m just going to list some reasons (I may leave some out because there are so many):
- Code re-use by developers: Stored procedures invite code re-use by developers. Why write it twice when someone else has already written it? This also encourages ease of maintenance by only storing complex logic once.
- Centralized code location: Using stored procedures provides a single location to store data access routines, making it easier to maintain the application. If bugs are found in the data access portion of the code the application will not need to be re-built and re-deployed; a re-compilation of the stored procedure will correct the bug.
- Multiple code techniques available: Versus views or direct table access, stored procedures also allow the use of flow-of-control language, parameters, variables, and error detection.
- Code re-use by the database engine: SQL Server will compile and cache the execution plan of each stored procedure so that it can be re-used, saving the cost of compiling and providing fast and reliable performance of the code.
- Reduced network traffic: Using stored procedures eliminates the need to push all of the data access code across the network each time it is executed; only the call statement is sent. In addition, specifying only the data required to be returned (both column and row filters) can greatly reduce the amount of data sent back and forth between the application and the database. Complex logic is another issue here; by placing complex data access logic in procedures all of the data access and comparison is performed on the database server. Only the final results are sent back to the calling object.
- Better security for the data: Using stored procedures provides better security in a couple of ways. First, since the data access code is not stored in the application or web page logic, no one can grab the access code and determine the database structure from it. Second, restricting access to the database to a user to only “execute” standard pieces of code allows for better security of the data; no one is allowed “carte blanche” access to the data. That way, should the SQL login(s) be compromised, the data security should still be largely in-tact. Third, removing table access largely eliminates the risk of SQL injection attacks.
How to I create and execute a stored procedure? Here is the basic syntax:
Use this to create a procedure:
CREATE PROCEDURE [schema name.] procedure name (optional parameters) AS…
Or this to modify an existing procedure:
ALTER PROCEDURE [schema name.] procedure name (optional parameters) AS…
Use this to execute a procedure:
EXEC (or EXECUTE) [schema name.] procedure name parameters…
How can I get the source code for my procedure? There are many ways to do this. You can use SSMS (right-click on the procedure in the Object Explorer and select one of the scripting options). Some people prefer to use T-SQL. The code snippet below shows 2 different methods using T-SQL. The first method directly accesses the sys.sql_modules table (the system table that stores the source code). This method dumps the source code for you. It appears to be pretty much unformatted, but if you copy the definition into another window you will discover that it does still contain the carriage returns, tabs, etc. I like this method because I can use the query to find procedures that contain keywords (like a particular column name, for instance). The second method uses a system procedure sp_helptext to dump the source code. It formats the source code for you in the results screen.
-- Use this method to query the system tables directly SELECT o.name, m.definition FROM sys.sql_modules m JOIN sys.objects o on m.object_id = o.object_id WHERE o.name = 'your procedure name'; -- use this method to make use of the system procedure sp_helptext Exec sp_helptext 'your procedure name';
To document, or not to document? Should you document your procedures? By all means, YES. And please document as you write them; don’t wait until the end or you will never do it. Please include a maintenance log near the top (AFTER the CREATE PROCEDURE statement so that the documentation will always be kept with the source code in the system tables!). Also include notes on any logic that you deem necessary, including any “code” values that mean something. I try to use this rule: I never assume that I will be the next developer changing this code, so I leave instructions behind to make it easier for the next person.