articles >> developer >> Create and Manage SQL Server Stored Procedures ...

Create and Manage SQL Server Stored Procedures using Transact-SQL

By : Randy Dyess Jun 29, 2002

As we learn more about SQL Server and Transact-SQL, it is easy to become overwhelmed with the amount of information out there. I can recall hundreds of times when I have said, "I know there is a (stored proc, DBCC command, SQL statement, etc.) out there that does that, I just can't remember its name." I thought I would deviate from my normal article style and see if writing an article that groups stored procs, DBCC commands, and SQL statements by their main or secondary usage would help others. My current job involves creating and maintaining hundreds of stored procedures, so I thought I would put together objects that I knew about that are used in the creation and maintenance of SQL Server stored procedures and extended stored procedures in SQL Server 7.0 and SQL Server 2000 (I'm not going to list or discuss objects used to obtain users permissions as I feel that those objects are best left for another article).

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'