Stored Procedure

Stored Procedure Note : SET @Pos = CHARINDEX(',', @OrderList, 1)

Although at its core a stored procedure is simply a named series of SQL statements just like any other query, a stored procedure offers several distinct advantages over sending the equivalent statement (or statements) as a regular query. Advantages: First, after SQL Server parses and compiles a stored procedure, it caches the execution plans in its procedure cache. When you run the same stored procedure again, SQL Server can re-use the cached execution plan instead. For this reason, stored procedures execute faster than if you execute their SQL statements individually. Second, if you call stored procedures from your applications instead of explicitly writing the queries into your applications, it is much easier for you to change a query within a stored procedure than it is to search for and change the code in an application. Another advantage of stored procedures is that you typically batch your SQL statements within them. Batches offer you enhanced performance because SQL Server can process all of the statements together instead of individually. You can also see that executing a stored procedure that contains SQL batches will reduce your network traffic. This is because SQL Server can send the results set for all of the statements in the batch at the same time rather than individuall. Some of the features of stored procedures include: Can contain virtually any SQL statement, including commands to execute other stored procedures. Can accept input parameters and generate output parameters. Capable of returning a status code to indicate what happened during the execution of the stored procedure. SQL Server supports three types of stored procedures. 1) User Define Stored Procedure 2) System stored Procedure (prefix: sp_ procedurename) 3) Extended Store Procedure (prefix: xp_ procedurename)

System and Extended Stored Procedures All this Stored Procedure available in MASTER Database. So that it’s available to all database tables. For e.g. – System Procedure

sp_helptext stored procedure enables you to view the definition of a view or stored procedure. For e.g. – Extended Procedure

xp_logininfo  stored procedure enables you to view the logins created for the Database tables.

Creating Stored Procedures CREATE PROCEDURE procedure_name [WITH option] AS sql_statement [...n] GO e.g. create procedure us_getInfo as select * from student order by name

Your stored procedure can refer to just about anything in a database, including tables, user-defined functions, views, other stored procedures, and temporary tables. Keep in mind that if you design your stored procedure to create a temporary table, that table’s available to you only while the stored procedure is executing.

Limitations You can’t include the following statements in a stored procedure: CREATE DEFAULT CREATE PROCEDURE CREATE RULE CREATE TRIGGER CREATE VIEW

Using Parameters in Stored Procedures You can use parameters in stored procedures to make them more interactive. You can use both input and output parameters. Input parameters enable you to pass a value to a variable within the stored procedure. In contrast, output parameters return a value after you run a stored procedure. You can use output parameters to return information to a calling stored procedure. You can define a total of 1,024 parameters in a stored procedure.

Input Parameters Use the following syntax to define an input parameter: CREATE PROCEDURE procedure_name [@parameter_name data_type] [= default_value] [WITH option] AS sql_statement [...n]

Use the following syntax to pass a value by reference: EXEC procedure_name @parameter_name = value

In this syntax, you replace @parameter_name with the name you want to assign to the parameter, and data_type with the data type (such as char, varchar, and so on). You should typically define a default value for the parameter so that the stored procedure will run successfully in the event its user doesn’t supply a value. You can use either constants (character strings or numeric values) or null for the default value. e.g.

CREATE PROCEDURE dbo.MovieByRating @rating varchar(5) = null AS SELECT rating, title FROM movie WHERE rating = @rating ORDER BY title GO

Output Parameters You can use output parameters to return a value from a stored procedure. This value can then be used by whatever method you used to call the stored procedure. For example, you might have two stored procedures: the first stored procedure calls the second, and the second procedure then returns a value to the first procedure. You might also simply call a stored procedure from a SQL statement,

and then use the value in the output parameter in a subsequent SQL statemen Use the following syntax to define an output parameter: CREATE PROCEDURE procedure_name [@parameter_name data_type] [= default_value] OUTPUT [WITH option] AS SQL statement [...n] e.g. CREATE PROC count_rows @movie_count int OUTPUT, @cust_count int OUTPUT, @cat_count int OUTPUT, @rental_count int OUTPUT, @rd_count int OUTPUT AS SELECT @movie_count = COUNT(*) FROM movie SELECT @cust_count = COUNT(*) FROM customer SELECT @cat_count = COUNT(*) FROM category SELECT @rental_count = COUNT(*) FROM rental SELECT @rd_count = COUNT(*) FROM rental_detail GO

Executing a Stored Procedure With Output Parameters DECLARE @movie_count int, @cust_count int, @cat_count int, @rental_count int, @rd_count int EXEC count_rows @movie_count OUTPUT, @cust_count OUTPUT, @cat_count OUTPUT, @rental_count OUTPUT, @rd_count OUTPUT SELECT @movie_count AS movie, @cust_count AS customer, @cat_count AS category,@rental_count AS rental, @rd_count AS rental_detail

Creating Custom Error Messages Use the following syntax to add custom error messages with the sp_addmessage stored procedure: EXEC sp_addmessage @msgnum = number, @severity = severity_level, @msgtext = 'Text of error message.', @with_log = 'true' or 'false'

Severity Level Used to Indicate 0 or 10 Errors in information entered by the user. These messages are considered informational. 11 through 16 Errors that can be corrected by the user. 17 Insufficient resources (such as locks or disk space). 18 Nonfatal internal errors. These errors usually indicate an internal software problem. 19 That an internal non-configurable limit in SQL Server was exceeded. 20 through 25 Fatal errors.

As a general rule, you should use either 0 or 10 for informational messages. SQL Server considers error messages with a severity greater than 20 as fatal and terminates the client’s connection to the server. Use 15 as the severity level for warning messages and 16 and higher as the severity level for errors. RAISERROR (msg_id|msg_txt, severity_level, state) [WITH LOG] CREATE PROC dbo.DeleteCust @cust_num cust_num = null AS IF EXISTS (SELECT cust_num FROM rental WHERE cust_num = @cust_num) BEGIN RAISERROR(50001, 10, 1) RETURN END DELETE FROM customer WHERE cust_num = @cust_num GO

Example Let's take a look at a practical example. Assume we have the table shown at the bottom of this page, named Inventory. This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following: SELECT Product, Quantity FROM Inventory WHERE Warehouse = 'FL' This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the

table information. We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code: CREATE PROCEDURE sp_GetInventory @location varchar(10) AS SELECT Product, Quantity FROM Inventory WHERE Warehouse = @location Our Florida warehouse manager can then access inventory levels by issuing the command EXECUTE sp_GetInventory 'FL' The New York warehouse manager can use the same stored procedure to access that area's inventory. EXECUTE sp_GetInventory 'NY' Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time. Now that you've learned the benefits of stored procedures, get out there and use them! Try a few examples and measure the performance enhancements achieved -you'll be amazed!