How to create and use Stored Procedures in SQL Server 2005

Creating a Stored Procedure

To create a user-defined stored procedure, you use the CREATE PROCEDURE

statement. You can create a stored procedure only in the current database.

In a T-SQL user-defined stored procedure, you cannot use some T-SQL statements

inside the CREATE PROCEDURE statement. These are listed here:

� CREATE DEFAULT

� CREATE PROCEDURE

� CREATE RULE

� CREATE TRIGGER

� CREATE VIEW

 

To create a stored procedure, you need certain permissions. You need the

CREATE PROCEDURE permission in the database. You also need the ALTER

SCHEMA permission in that database, because stored procedures are schemascoped

objects.

To create a CLR stored procedure, you need to own the assembly referenced

or have the REFERENCES permission on that assembly.

 

Creating a procedure without parameters

In describing how to create a stored procedure, I assume that you have the

permissions specified in the preceding section. If you are unfamiliar with how

to grant permissions, see Chapter 11 on security in SQL Server 2005.

This example creates a stored procedure that retrieves name and e-mail

information about contacts in the Person.Contact table of the

AdventureWorks database. To do that, use this T-SQL code:

 

USE NIR

GO

create procedure sp_GetSal

as

select NIR.dbo.emp.Name , NIR.dbo.emp.Salary from NIR.dbo.emp

go

 

The first line of the CREATE PROCEDURE statement specifies the name of the

stored procedure you create. Notice that the name of the stored procedure

includes a schema name, emp. Because the stored procedure is in a

schema other than the dbo schema, you run no risk of naming problems

occurring if Microsoft adds a similarly named system stored procedure

(which goes in the dbo schema) in a future version of SQL Server.

To run the stored procedure, run this code:

 

EXEC emp.sp_GetSal

 

If you want to return the NIR database to its original state, you

can drop the stored procedure by using the following code:

 

DROP PROCEDURE emp.sp_GetSal

 

Creating a stored procedure with a parameter

The stored procedure makes the same selection as in the preceding

stored procedure except that it has a single parameter. Use this code:

 

create procedure sp_GetSal_par

@id int

as

select NIR.dbo.emp.Name , NIR.dbo.emp.Salary from NIR.dbo.emp where Id=@id

go

 

Notice in the fourth line of the code, you specify a parameter @id

whose type is int. That parameter is used in the WHERE clause of

the SELECT statement.

To select salary details where the id of the emp is 1, use the

following code:

 

exec sp_GetSal_par 1

 

To remove the sp_GetSal_par stored procedure and return the NIR database to its

original state, use the following code:

 

DROP PROCEDURE sp_GetSal_par

 

Calling a Stored Procedure

Calling a stored procedure is straightforward. Use the EXEC statement together

with the name of a system stored procedure or a user-defined stored procedure.

Because a user-defined stored procedure is associated with an individual

database, you must ensure that the correct database is the current database.

To execute a user-defined stored procedure called Person.sp_getContact

Names that is associated with the AdventureWorks database, use the following

code:

USE NIR

GO

EXEC sp_GetSal_par

 

The preceding code works with whatever the current database is when you execute

it. The USE statement specifies that the current database is Adventure

Works before executing the sp_GetSal_par stored procedure.

 

CLR Stored Procedures

The presence of the Common Language Runtime (CLR) in SQL Server is new

in SQL Server 2005. One of the uses for .NET language code is to create stored

procedures written in a .NET language.

 

To create a CLR stored procedure, you need to follow these broad steps:

� Create a static method of a class using your .NET language of choice.

 

� Using the relevant language compiler, compile the class into an assembly.

 

� Register the assembly in SQL Server 2005 using the CREATE ASSEMBLY

statement.

 

� When the assembly is registered, you can use the CREATE PROCEDURE

statement to create a stored procedure that runs the method you created.