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, ect) 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
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 will
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'
|
Now that we have a stored procedure built in SQL Server or a extended stored
procedure added to the master database we have several ways to return
information about that stored procedure to our users or to our code.
Determine the stored procedures or extended stored procedures currently in the
database:
SP_STORED_PROCEDURES
System stored procedure which returns a list of stored procedures in the
current environment.
Syntax
sp_stored_procedures [[@sp_name =] 'name']
[,[@sp_owner =] 'owner']
[,[@sp_qualifier =] 'qualifier']
|
SP_HELPEXTENDEDPROC
System stored procedure that displays the currently defined extended stored
procedures and the name of the dynamic-link library to which the procedure
belongs too.
Syntax
sp_helpextendedproc @funcname = 'procedure'
|
Determine the ID number of the stored procedure
@@PROCID
Metadata function returning an INTEGER representing the current stored
procedure's identifier number.
Syntax
@@PROCID
|
OBJECT_ID
Metadata function that returns an INTEGER representing the identification
number for the given object name.
Syntax
OBJECT_ID ('object')
|
Determine the name of a stored procedure given the ID number:
OBJECT_NAME
Metadata function that returns a NCHAR representing the database name for the
given object identification number.
Syntax
OBJECT_NAME (object_id)
|
Determine an object dependencies:
SP_DEPENDS
System stored procedure which displays information about database object
dependencies.
Syntax
sp_depends [ @objname = ] 'object'
|
Return properties, columns, parameters and the text of a stored procedure:
OBJECT_PROPERTY
Metadata function that will return an INTEGER representing information about a
specified object in the current database.
Syntax
OBJECTPROPERTY (id, 'property')
|
SP_SPROC_COLUMNS
System stored procedure which returns column information for a specified stored
procedure or user-defined function.
Syntax
sp_sproc_columns [[@procedure_name =] 'name']
[,[@procedure_owner =] 'owner']
[,[@procedure_qualifier =] 'qualifier']
[,[@column_name =] 'column_name']
[,[@ODBCVer =] 'ODBCVer']
|
COLUMNPROPERTY
Metadata function that returns an INTEGER representing information about a
column in a stored procedure or table given the stored procedure or table ID,
the column name and the property of the column you are asking about.
Syntax
COLUMNPROPERTY ( id , 'column' , ' property' )
|
SP_HELPTEXT
System stored procedure that prints the text of a rule, a default, or an
unencrypted stored procedure, user- defined function, trigger, or view.
Syntax
sp_helptext @objname = 'name'
|
INFOMATION_SCHEMA.PARAMETERS
Informational schema view located in the master database (SQL Server 2000) or
all databases (SQL Server 7.0) containing one row for each parameter of a
user-defined function or stored procedure accessible to the current user in the
current database.
|
|
