Creating Stored Procedures

The creation process depends on what we want it to do – we can either build the whole procedure immediately, or build the T-SQL first (for more complex solutions), check its performance, and finally, place it in a stored procedure. Both ways require us to wrap the T-SQL code with the same CREATE PROCEDURE command.

Now let's take a look at the syntax for creating a stored procedure:

     CREATE PROCEDURE procedurename [parameter1 datatype [length] [OUTPUT],     parameter2...]     AS     BEGIN     ...     END 

An example of a simple stored procedure follows, where two numbers are passed in and the midpoint of the two numbers is listed:

     CREATE PROCEDURE ut_MidPoint @LowerNumber int, @HigherNumber int     AS     BEGIN       DECLARE @Mid int       IF @LowerNumber > @HigherNumber         RAISERROR('You have entered your numbers the wrong way round',16,1)       SET @Mid = ((@HigherNumber - @LowerNumber) / 2) + @LowerNumber       SELECT @Mid     END 

At the time of creation, SQL Server takes our code and parses it for any syntactical errors. Column names and variables are checked for existence at compilation. Even if they don't exist, any temporary tables created within the stored procedure will also pass the compilation stage. This is known as deferred name resolution. It can be an asset or a drawback, as we can create temporary tables that exist for the lifetime of the stored procedure execution only, which is desirable, but if we define a temporary table and get something wrong later on, such as a column name, then the compilation will not pick up this error.

While executing the CREATE PROCEDURE statement, errors will be reported. Once it is compiled, the details of the stored procedure are stored in three system tables in the concerned database:

• sysobjects
  This table contains a row for each object that is created within the database. If you want to track an object in the database, you can use this as the main root. Apart from the object name and type, this table also keeps track of the object's owner and time of creation. This can be useful in crosschecking the contents of our source control system with our database.

• sysdepends
  This table stores dependency information about objects. For example, when a stored procedure, view, or trigger is created, there can be references to other tables, views, or procedures within it. These references are known as dependencies. If one of the dependent objects alters, we may need to recompile the stored procedure. For example, if we alter an index on a table, we should recompile every dependent object.

• syscomments
  This holds the original SQL definition statements of the stored procedure. It also holds details of views, rules, defaults, triggers, CHECK constraints, and DEFAULT constraints.

It is possible to interrogate these data. By looking for rows with a P in the sysobjects table, we can obtain all the stored procedures. From that, we can find out when the stored procedure was created, and also move to the syscomments table by using the ID to retrieve detailed information about the procedure itself, like, if it is encrypted or not.

These tables are safe only for interrogation, although details within them can always change between SQL Server releases. One of the best methods to ensure that the code stored in your source control system matches that within your database is to check if the dates and times match or are within a few seconds of each other.

Important

Although we have mentioned these tables, we strongly advise that you should never alter any information in these tables directly.

Performance Consideration

When a stored procedure is created, it passes through several steps. First of all, the T-SQL is parsed and resolved, saved to the disk, and stored in SQL Server. The first time the procedure is executed, the procedure is retrieved and optimized, on the basis of any data queries and values passed through parameters. SQL Server will inspect the code and try to use the best indexes on the tables, which are referenced by checking the statistics that are held for those tables.

The query plan is then cached within SQL Server, ready for any further executions. SQL Server will always use this plan, providing it doesn't retire the plan. Thus, the performance gain of stored procedures comes from compiled cached plans.

Network Consideration

You may consider passing T-SQL statements to insert a row into a table, with very few characters. However, creating a stored procedure and passing only the name of the stored procedure, parameters, and their values reduces the characters needed. We can see the extra overhead imposed by the T-SQL statements on our network by multiplying this difference by the number of calls being made. This can be a significant issue, especially if we are using it over the Internet.

Compare the next two statements with each other:

     INSERT INTO EmployeeTerritories (EmployeeID, TerritoryID)     VALUES (3,12345)   Ins_EmployeeTerritories @empId=3,@terrId=12345 

The first statement has 74 characters, while the second has 46 characters, differing by a mere 28 characters. However, if this was a more column-intensive insert, with 10,000 of these in a day, for example, this amounts to almost 280k of wasted bandwidth! What if an image data type was being uploaded or downloaded? Anything that is of binary data type, such as images or sounds, and so on, is sent as binary values. These are converted to character strings, and this will double the size of the ad-hoc query that we are sending, when using T-SQL inline.