SQL Server® 2008 Administration: Real World Skills for MCITP Certification and Beyond

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 ...