Stored Procedures

Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server. The IF statement in this example shows embedding conditional logic in a procedure to keep from sending a result set to the application.

Applications do not need to transmit all of the SQL statements in the procedure: they have to transmit only an EXECUTE or CALL statement containing the name of the procedure and the values of the parameters.

Stored procedures can also shield users from needing to know the details of the tables in the database. If a set of stored procedures supports all of the business functions users need to perform, users never need to access the tables directly; they can just execute the stored procedures that model the business processes with which they are familiar.

 You can reuse this API across multiple applications and multiple programming languages. This technique avoids duplicating database code, saving time and effort when you make updates due to schema changes, tune the performance of queries, or add new database operations for logging, security, and so on. 

Benifits 


They allow modular programming.

 You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.

They allow faster execution.

If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

They can reduce network traffic.

An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

They can be used as a security mechanism.

Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly. 

When creating a stored procedure you can either use CREATE PROCEDURE or CREATE PROC.  After the stored procedure name you need to use the keyword "AS" and then the rest is just the regular SQL code that you would normally execute.
On thing to note is that you cannot use the keyword "GO" in the stored procedure.  Once the SQL Server compiler sees "GO" it assumes it is the end of the batch.
Also, you can not change database context within the stored procedure such as using "USE dbName" the reason for this is because this would be a separate batch and a stored procedure is a collection of only one batch of statements.

SELECT * FROM DatabaseName.TableName.ColumnName

To create a stored procedure to do this the code would look like this:

CREATE PROCEDURE storedProcedureName
AS
SELECT * FROM DatabaseName.TableName.ColumnName
GO

To call the procedure to return the contents from the table specified, the code would be:

EXEC storedProcedureName
--or --
storedProcedureName

Stored procedure with parameters

The real power of stored procedures is the ability to pass parameters and have the stored procedure handle the differing requests that are made.  In this topic we will look at passing parameter values to a stored procedure.
Just like you have the ability to use parameters with your SQL code you can also setup your stored procedures to accept one or more parameter values.

One Parameter

In this example we will query the Person.Address table from the AdventureWorks database, but instead of getting back all records we will limit it to just a particular city.  This example assumes there will be an exact match on the City value that is passed.

CREATE PROCEDURE uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

To call this stored procedure we would execute it as follows:

EXEC uspGetAddress @City = 'New York'

We can also do the same thing, but allow the users to give us a starting point to search the data.  Here we can change the "=" to a LIKE and use the "%" wildcard.

CREATE PROCEDURE uspGetAddress @City nvarchar(30) 
AS 
SELECT * 
FROM AdventureWorks.Person.Address 
WHERE City LIKE @City + '%' 
GO

In both of the proceeding examples it assumes that a parameter value will always be passed. If you try to execute the procedure without passing a parameter value you will get an error message such as the following:

Msg 201, Level 16, State 4, Procedure uspGetAddress, Line 0
Procedure or function 'uspGetAddress' expects parameter '@City', which was not supplied.

Default Parameter Values

In most cases it is always a good practice to pass in all parameter values, but sometimes it is not possible. So in this example we use the NULL option to allow you to not pass in a parameter value.  If we create and run this stored procedure as is it will not return any data, because it is looking for any City values that equal NULL.

CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

We could change this stored procedure and use the ISNULL function to get around this.  So if a value is passed it will use the value to narrow the result set and if a value is not passed it will return all records. (Note: if the City column has NULL values this will not include these values. You will have to add additional logic for City IS NULL)

CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
GO

Multiple Parameters

Setting up multiple parameters is very easy to do.  You just need to list each parameter and the data type separated by a comma as shown below.

CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'
GO

To execute this you could do any of the following:

EXEC uspGetAddress @City = 'Calgary'
--or
EXEC uspGetAddress @City = 'Calgary', @AddressLine1 = 'A'
--or
EXEC uspGetAddress @AddressLine1 = 'Acardia'
-- etc...


Returning stored procedure parameter values to a calling stored procedure

In a previous topic we discussed how to pass parameters into a stored procedure, but another option is to pass parameter values back out from a stored procedure.  One option for this may be that you call another stored procedure that does not return any data, but returns parameter values to be used by the calling stored procedure. 
Setting up output paramters for a stored procedure is basically the same as setting up input parameters, the only difference is that you use the OUTPUT clause after the parameter name to specify that it should return a value.  The output clause can be specified by either using the keyword "OUTPUT" or just "OUT".
Simple Output

CREATE PROCEDURE uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT
AS
SELECT @AddressCount = count(*) 
FROM AdventureWorks.Person.Address 
WHERE City = @City

Or it can be done this way:

CREATE PROCEDURE uspGetAddressCount @City nvarchar(30), @AddressCount int OUT
AS
SELECT @AddressCount = count(*) 
FROM AdventureWorks.Person.Address 
WHERE City = @City

To call this stored procedure we would execute it as follows.  First we are going to declare a variable, execute the stored procedure and then select the returned valued.

DECLARE @AddressCount int
EXEC uspGetAddressCount @City = 'Calgary', @AddressCount = @AddressCount OUTPUT
SELECT @AddressCount
This can also be done as follows, where the stored procedure parameter names are not passed.
DECLARE @AddressCount int
EXEC uspGetAddressCount 'Calgary', @AddressCount OUTPUT
SELECT @AddressCount

0 comments:

Post a Comment