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