STORED PROCEDURES By Adrienne Watt Benefits of Stored

STORED PROCEDURES By Adrienne Watt

Benefits of Stored Procedures 2 Stored procedures are vital to any database scheme. Database developers, as well as database administrators, often write their own stored procedures to run commonly performed administrative tasks or to apply a complex business rule. These types of procedures can contain controlof-flow structures, data modifications or data retrieval statements, cursors, and errorhandling statements.

Benefits of Stored Procedures 3 Stored procedures can be Simple – one Select statement Complex – multiple Select statements using control-of-flow statements. Benefits: Speed – optimized Share application logic – small sp can be incorporated with other sp Security – give Execute rights only – no table access

Benefits of Stored Procedures 4 Utilizing stored procedures has many benefits, including: Faster execution due to precompiled code. Encapsulated Increased Greater business rules. security. modularity.

Create Stored Procedures 5 You can create stored procedures using the CREATE PROCEDURE statement. Before creating a stored procedure, consider that: CREATE PROCEDURE statements cannot be combined with other SQL statements in a single batch. You can create a stored procedure in the current database. Objects that you reference must exist when you execute the stored procedure.

Create Stored Procedures 6 Creates a stored procedure (a precompiled collection of SQL statements) that can take and/or return user-supplied parameters. CREATE PROCedure [owner. ]procedure_name [(parameter 1 [, parameter 2]. . . [parameter 255])] AS sql_statements

Creating Stored procedure 7 1. Start and login to SQL Server Management Studio. 2. Select the pubs database. 3. Click on New query button. 4. In the Query window, type the following statement CREATE PROCEDURE spshw. Authors AS SELECT au_lname, au_fname FROM AUTHORS

Create Stored Procedures 8

Create Stored Procedures 9 This stored procedure returns all authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure does not use any parameters. Can be a view. CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a. au_id = ta. au_id INNER JOIN titles t ON t. title_id = ta. title_id INNER JOIN publishers p ON t. pub_id = p. pub_id GO

Create Stored Procedures 10 This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed. CREATE PROC au_info ( @lastname varchar(40), @firstname varchar(20) ) AS SELECT au_lname, au_fname, title, pub_name FROM authors, titles, publishers, titleauthor WHERE au_fname = @firstname AND au_lname = @lastname AND authors. au_id = titleauthor. au_id AND titles. title_id = titleauthor. title_id AND titles. pub_id = publishers. pub_id

Stored Procedures 11 The au_info stored procedure can be executed in these ways: EXECUTE au_info 'Dull', 'Ann' -- Or EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'

Stored Procedure with If statements 12 CREATE PROCEDURE Simple. Proc AS DECLARE @count int DECLARE @msg varchar(255) SELECT @count = count(*) FROM authors WHERE city = 'Oakland' IF @count > 0 SET @msg = convert(char(2), @count) + ' authors live in Oakland' Else SET @msg = 'Oakland is culturally deprived' select @msg

If. . Else & Begin. . End 13 CREATE PROCEDURE Proc 2 AS DECLARE @msg varchar(255) IF (SELECT COUNT(title_id) FROM titles WHERE price BETWEEN 10 AND 20) > 0 BEGIN SET @msg = 'There are several books that are a good value between $10 and $20. These books are: ' PRINT @msg SELECT title FROM titles WHERE price BETWEEN 10 AND 20 END ELSE BEGIN SET @msg = 'There are no books between $10 and $20. You might consider the following books that are under $10. ' PRINT @msg SELECT title FROM titles WHERE price < 10 END

If. . Else & Begin. . End 14 CREATE PROC title_choice @titletype varchar(10), @lowprice money, @highprice money AS DECLARE @msg varchar(255) IF (SELECT COUNT(title_id) FROM titles WHERE type = @titletype AND price BETWEEN @lowprice AND @highprice) > 0 BEGIN SET @msg = 'There are several books that are a good value between the prices you chose. These books are: ‘ PRINT @msg SELECT title FROM titles WHERE type = @titletype AND price BETWEEN @lowprice AND @highprice END ELSE BEGIN SET NOCOUNT ON SET @msg = 'There are no books between the prices you chose. You might consider the following books. ' PRINT @msg SELECT title FROM titles WHERE type = @titletype AND price < @lowprice END

Output 15 CREATE PROCEDURE newsum @sum 1 smallint, @sum 2 smallint, @sum 3 smallint, @sum 4 smallint, @mysum smallint OUTPUT AS SELECT @mysum = (@sum 1+@sum 2+@sum 3+@sum 4) GO DECLARE @show. Sum smallint EXEC newsum 2, 4, 6, 8, @show. Sum OUTPUT SELECT ‘The sum of these numbers is: ‘, @show. Sum GO