Create and Manage SQL Server Stored Procedures using Transact-SQL

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’

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |