Create and Manage SQL Server Stored Procedures using Transact-SQL

One of the things we learn as beginning DBAs or Transact-SQL programmers is how to create and delete stored procedures. Later as our skills progress, we learn how to add or delete an association with an extended stored procedure to add a whole new world of functionality to our databases. SQL Server provides multiple documented and undocumented statements and system stored procedures that we can use to create new stored procedures, drop existing stored procedures, and associate/disassociate extended stored procedures in our databases (remember: extended stored procedure are only maintained in the master database).

Create a SQL Server Stored Procedure

CREATE PROCEDURE
Data Definition Language Statement that creates a new stored procedure.
Permissions default to members of the sysadmin server role, the db_owner and db_ddladmin database roles and can be transferred by members of the sysadmin and db_owner roles.
Syntax
CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]
[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
 

Add (associate) a SQL Server Extended Stored Procedure

DBCC ADDEXTENDEDPROC
Database console command which can be used to add an extended procedure.
Syntax
DBCC ADDEXTENDEDPROC ('@procname', '@dll')
SP_ADDEXTENDEDPROC
System stored procedure located in the master database that registers the name of a new extended stored procedure to SQL Server. Permissions default to members of the sysadmin server role and are not transferable.
Syntax
sp_addextendedproc [ @functname = ] 'procedure' ,
    [ @dllname = ] 'dll'
 

Drop a SQL Server Stored Procedure

DROP PROCEDURE
Data Definition Language statement used to remove one or more stored procedures or procedure groups in a database. Permissions default to the owner of the stored procedure and members of the sysadmin server role and db_owner and db_ddladmin database roles and are not transferable. Syntax
DROP PROCEDURE {procedure} [,…n]
SP_MSDROP_OBJECT
System stored procedure which can be used to drop a database object.
Permissions default to members of the sysadmin server role and db_owner database role and the owner of the database and are transferable. Syntax
sp_MSdrop_object [@object_id] [, @object_name] [, @object_owner]
 

Remove (disassociate) a SQL Server Extended Stored Procedure

DBCC DROPEXTENDEDPROC
Database console command which can be used to drop an extended procedure. Syntax
DBCC DROPEXTENDEDPROC(‘@procedurename’)
SP_DROPEXTENDEDPROC
System stored procedure that drops an extended stored procedure.
Permissions default to members of the sysadmin fixed server role and are not transferable. Syntax
sp_dropextendedproc @functname = ‘procedure’

Continues…

Leave a Reply

Your email address will not be published. Required fields are marked *