Entire Site
Free Trial
Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.
- Create BookmarkCreate Bookmark
- Create Note or TagCreate Note or Tag
- DownloadDownload
- PrintPrint
Chapter 11: Triggers and Stored Procedures > Creating Stored Procedures
Creating Stored Procedures
Stored procedures are created with the CREATE PROCEDURE statement. The syntax used is as follows:
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
The following example creates a stored procedure named disable_login that can be used to disable a SQL Login account and log a timestamp to the Admin_actions table in the master database:
CREATE PROCEDURE dbo.disableuser
@DenyLoginName varchar(50)
AS
DECLARE @tempstr varchar (1024)
SET @tempstr = 'DENY CONNECT SQL TO ' + @DenyLoginName
PRINT @tempstr
EXEC (@tempstr)
INSERT INTO master.dbo.admin_actions
VALUES(
GETDATE(),
'User disabled: ' + @DenyLoginName
)
GO