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.
No comments :
Post a Comment