System Stored Procedures

System Stored Procedures There are two main types of stored procedure – system stored procedures and userdefined stored procedures. System Stored Procedure Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures. A user stored procedure is any program that is stored and compiled within SQL Server (but not in the master database).

Creating Stored Procedures CREATE PROCEDURE procedurename [parameter1 datatype [length] [OUTPUT], parameter2...] AS BEGIN ... END Eg: 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 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. Why Use Stored Procedures? By using stored procedures, we can reduce the time a process can take, as stored procedures are compiled. Another gain from using stored procedures is that they are much simpler to maintain, as compared to raw T-SQL code. Since the stored procedure is held centrally within the database, any required bug fix, upgrade, or modification can be completed centrally, thus reducing the downtime. The code in a stored procedure is executed in a single batch of work. This means that it is not necessary to include a GO command while writing code for procedures. SQL Server will take any of those statements and implicitly execute them, as a batch. Perhaps, the greatest reason to create a stored procedure rather than use inline T-SQL, is security. Designing Good Stored Procedures 1. As with any other programming language – keep it simple. 2. Document your code, especially if you have to create a complex section of code. Throughout the stored procedure, place comments with a description of the stored procedure and any changes made at the top. 3. At processing time, T-SQL works well with a set. Therefore, avoid cursors wherever possible; even if it means using two or three steps or maybe even a temporary table 4. Always clean up. If you have used a cursor, close and deallocate it, so that memory and resources are freed up. Similarly, if you are using temporary tables, ensure that you explicitly drop them.

5. When a stored procedure is complete either through an error or a successful process, ensure that you return a value. Data integrity Within a database, there are three different levels of integrity – domain, entity, and referential. Domain integrity Domain integrity ensures that the values for a specific column are valid and meet the relevant business rules. This is enforced through CHECK constraints, DEFAULT values, and FOREIGN KEY constraints. Entity integrity Entity integrity ensures that every row within a table is unique. This integrity is enforced through UNIQUE constraints and PRIMARY KEYS. Referential integrity Referential integrity ensures that relationships between tables are maintained. It can be enforced through FOREIGN KEYS as well as cascading updates. This is the only type of integrity that is enforced by stored procedures. Flow control Code Blocks/ BEGIN…END When working with decisions, functions, or loops, there will be many instances when we have to execute more than one line of code. In programming languages, we will find END IF statements or LOOP … UNTIL structures where there are keywords for defining the beginning and end of the block. This is not the case within SQL Server, which uses a BEGIN … END structure instead. CASE Unlike the IF statement that is placed in code as part of a decision-making process, CASE can be found within any of the SELECT, UPDATE, and INSERT T-SQL data statements. CASE statements won't work for DELETEs. To implement the CASE statement, two different methods can be employed. The first method is known as the simple CASE function. Here, an expression, which is to be tested, is the same throughout the possible options available. Let's look at its syntax: CASE input_expression WHEN when_expression THEN result_expression [...n]

[ ELSE else_result_expression ] END [AS column_alias] Loops The WHILE Loop To repeat a block of code, SQL Server gives us the WHILE statement. This will continue executing the block and forming the loop, until either the Boolean expression returns FALSE or we BREAK out of the code. The syntax is: WHILE Boolean_expression { sql_statement | statement_block } [ BREAK ] { sql_statement | statement_block } [ CONTINUE ] BREAK breaks the loop and passes the control to the statements outside the code block, while the CONTINUE statement will resume processing from the beginning of the loop. Nesting in Stored Procedures Recursion is a scenario where a procedure calls itself. SQL Server puts a limit of 32 on the number of times that a stored procedure can be called by itself. However, things can go wrong even in that time frame, so it's always advisable to have a good ‘drop out’ scenario. CREATE PROCEDURE ut_Factorial @ValIn bigint, @ValOut bigint output AS BEGIN IF @ValIn > 20 BEGIN PRINT 'Invalid starting point. Has to be <= 20' RETURN -99 END DECLARE @WorkValIn bigint, @WorkValOut bigint IF @ValIn != 1 BEGIN SET @WorkValIn = @ValIn - 1 PRINT @@NESTLEVEL EXEC ut_Factorial @WorkValIn, @WorkValOut OUTPUT SET @ValOut = @WorkValOut * @ValIn END ELSE

SET @ValOut = 1