SQL Server Books Online: stored procedures and their creation

server| Create | Stored procedure stored procedures can make it much easier to manage the database and to display information about the database and its users. Stored procedures are precompiled collections of SQL statements and optional control flow statements, stored as a name and processed as a unit. Stored procedures are stored in a database that can be executed by an application through a single call, and allows the user to declare variables, conditional execution, and other powerful programming capabilities.
Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.

You can use stored procedures for any purpose that uses SQL statements, and it has the following advantages: You can execute a series of SQL statements in a single stored procedure.
You can reference other stored procedures from within your own stored procedures, which simplifies a series of complex statements.
Stored procedures are compiled on the server when they are created, so it executes faster than a single SQL statement.
The functionality of the stored procedure depends on the functionality provided by the database.
Creating a Stored Procedure
You can use Transact-SQL statement creation PROCEDURE to create stored procedures. Before you create a stored procedure, consider the following: You cannot combine the Create PROCEDURE statement with other SQL statements into a single batch.
The permission to create the stored procedure is owned by the database owner, who can grant this permission to another user.
A stored procedure is a database object whose name must conform to the rules for identifiers.
Stored procedures can only be created in the current database.
When you create a stored procedure, you specify: all input parameters and output parameters returned to the calling procedure or batch.
A programming statement that performs database operations, including calling other procedures.
Returns the status value of the calling procedure or batch to indicate success or failure (and the reason for failure). System stored Procedures
Many of the management activities in Microsoft®sql server™2000 are performed through a special process called system stored procedures. System stored procedures are created and stored in the master database with the sp_ prefix. You can execute system stored procedures from any database without using the master database name to fully qualify the name of the stored procedure.

It is strongly recommended that you do not create stored procedures that are prefixed with sp_. SQL Server always finds stored procedures that begin with sp_ in the following order: Find stored procedures in the master database.
Finds the stored procedure based on any qualifier (database name or owner) provided.
If the owner is not specified, the dbo is used as the owner to find the stored procedure.
Therefore, although a stored procedure created by a user with a sp_ prefix may exist in the current database, the master database is always checked first (even if the stored procedure is qualified with the database name).



Important If the stored procedure created by the user has the same name as the system stored procedure, the stored procedure created by the user is never executed.


Group
If you assign a different identification number to a procedure, you can create the procedure with the same name as an existing stored procedure, which allows you to logically group the procedures. Grouping procedures with the same name can be deleted at the same time. Procedures used in the same application are generally grouped in this way. For example, the process used to my_app an application might be named My_proc;1, My_proc;2, and so on. Delete My_proc deletes the entire group. After you have grouped the procedures, you cannot delete individual procedures within the group.
Temporary stored procedures
Private and global temporary stored procedures are similar to temporary tables and can be created by adding a # and # # prefix to the procedure name. # represents a local temporary stored procedure, # # represents a global temporary stored procedure. These procedures will no longer exist when SQL Server shuts down.

Temporary stored procedures are useful when connecting to earlier versions of SQL Server, and these earlier versions do not support the use of Transact-SQL statements or batch execution plans again. Applications that connect to SQL Server 2000 should use the sp_executesql system stored procedure instead of using temporary stored procedures. For more information, see Performing a scheduled cache and reuse.

This procedure can only be performed if a connection to the local temporary procedure is created, which is automatically deleted when the connection is closed (the user logs off from SQL Server).

Any connection can perform a global temporary stored procedure. The global temporary stored procedure does not exist until the connection used by the user who created the procedure is closed, and the current version of the procedure used by all other connections is completed. Once the connection used to create the procedure closes, it is no longer allowed to start the execution of the global temporary stored procedure. Only those connections that have started executing the stored procedure are allowed to complete the operation of the procedure.

If you create a stored procedure that does not have a # or # # prefix directly in the tempdb database, because tempdb is re-created every time you start SQL Server, the stored procedure is automatically deleted when you close SQL Server. A procedure created directly in tempdb exists even after the connection to which the procedure was created terminates. As with any other object, you can grant, deny, and revoke permissions to execute the temporary stored procedure to other users.
CREATE PROCEDURE
Creates a stored procedure that is a collection of Transact-SQL statements that can be saved to accept and return user-supplied parameters.

You can create a procedure for permanent use, or for temporary use in a session (local temporary procedures), or for temporary use in all sessions (global temporary procedures).

You can also create stored procedures that run automatically when the Microsoft®sql Server™ starts.
Grammar
CREATE PROC [edure] procedure_name [; number]
[{@parameter data_type}
[Varying] [= default] [OUTPUT]
] [,... N]

[With
{RECOMPILE | Encryption | RECOMPILE, encryption}]

[For REPLICATION]

As sql_statement [... n]

Parameters
Procedure_name

The name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique to the database and all of them. For more information, see Using Identifiers.

To create a local temporary procedure, you can precede procedure_name with a number character (#procedure_name), and to create a global temporary procedure, you can precede procedure_name with two number characters (# #procedure_name). The full name (including # or # #) cannot exceed 128 characters. Specifies that the name of the procedure owner is optional.

; number

is an optional integer that is used to group procedures with the same name so that a drop PROCEDURE statement can be used to remove the same group of procedures. For example, an application named orders uses a procedure that can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement drops the entire group. If the name contains a bounding identifier, the number should not be included in the identifier, and the appropriate delimiter should be used only before and after procedure_name.

@parameter

The parameters in the procedure. You can declare one or more parameters in the CREATE PROCEDURE statement. The user must provide the value of each declared parameter when the procedure is executed (unless the default value for that parameter is defined). A stored procedure can have up to 2.1 parameters.

Use the @ symbol as the first character to specify the parameter name. Parameter names must conform to the rules for identifiers. The parameters for each procedure are used only for the procedure itself, and the same parameter names can be used in other procedures. By default, parameters can only be substituted for constants and cannot be used in place of a table name, column name, or other database object name. For more information, see EXECUTE.

Data_type

The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures. However, the cursor data type can only be used for OUTPUT parameters. If you specify a data type of cursor, you must also specify both varying and OUTPUT keywords. For more information about the data types and their syntax provided by SQL Server, see Data types.





Indicates that there is no maximum number of restrictions for output parameters that can be cursor data types.



Varying

Specifies the result set that is supported as output parameters (dynamically constructed by stored procedures and content can be changed). Only cursor parameters are applicable.

Default

The default value for the parameter. If you define a default value, you do not have to specify the value of the parameter to execute the procedure. The default value must be constant or NULL. If the procedure uses the LIKE keyword for this parameter, the default value can contain wildcard characters (%, _, [], and [^]).

OUTPUT

Indicates that the parameter is a return parameter. The value of this option can be returned to Exec[ute]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. Output parameters that use the input keyword can be cursor placeholders.

N

Represents a placeholder that can specify up to 2.1 parameters.

{RECOMPILE | Encryption | RECOMPILE, encryption}

RECOMPILE indicates that SQL Server does not cache the schedule for the process, which will be recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and do not want to overwrite execution plans that are cached in memory.

Encryption represents an entry that contains the text of the CREATE PROCEDURE statement in the SQL Server encryption syscomments table. Use encryption to prevent the process from being published as part of SQL Server replication.





Description during the upgrade process, SQL Server uses the encrypted annotations stored in syscomments to recreate the encryption process.



For REPLICATION

Specifies that stored procedures created for replication cannot be performed at the Subscriber. Stored procedures created using the For REPLICATION option can be used as stored procedure filtering and can only be performed during the replication process. This option cannot be used with the WITH RECOMPILE option.

As

Specifies the action to be performed by the procedure.

Sql_statement

Any number and type of Transact-SQL statements to include in the procedure. But there are some restrictions.

N

is to indicate that this procedure can contain placeholders for more than one Transact-SQL statement.
Comments
The maximum size of the stored procedure is 128 MB.

A user-defined stored procedure can only be created in the current database (except for temporary procedures, which are always created in tempdb). In a single batch, the CREATE PROCEDURE statement cannot be used in combination with other Transact-SQL statements.

By default, the parameter can be empty. If you pass a null parameter value and the parameter is used in a CREATE or ALTER TABLE statement, and the column referenced in the statement does not allow NULL, SQL Server generates an error message. To prevent a null parameter value from being passed to a column that does not allow NULL, you should add programming logic to the procedure or use the default value for the column (using the defaults keyword of CREATE or ALTER TABLE).

It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER table statement in the stored procedure, such as when you create a temporary table. The ansi_dflt_on and Ansi_dflt_off options control how SQL Server assigns a null or NOT NULL attribute to a column (if not specified in the CREATE table or ALTER table statement). If a connection executes a stored procedure that sets these options differently than the connection that created the procedure, the table columns created for the second connection may have different nullability and behave differently. If NULL or NOT NULL is explicitly declared for each column, temporary tables are created with the same nullability for all connections that execute the stored procedure.

When you create or change a stored procedure, SQL Server saves the settings for SET QUOTED_IDENTIFIER and set ANSI_NULLS. These original settings are used when the stored procedure is executed. Therefore, the SET QUOTED_IDENTIFIER and set ANSI_NULLS settings for all client sessions are ignored when the stored procedure is executed. The SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur during a stored procedure do not affect the functionality of the stored procedure.

Other set options, such as set ARITHABORT, set ANSI_WARNINGS, or set ansi_paddings, are not saved when a stored procedure is created or changed. If the logic of a stored procedure depends on a specific setting, add a set statement at the beginning of the procedure to ensure that it is set correctly. When a set statement is executed from a stored procedure, the setting is valid only until the stored procedure completes. After that, the setting reverts to the value when the stored procedure was invoked. This allows individual clients to set the desired options without affecting the logic of the stored procedure.





Describes whether SQL Server interprets an empty string as a single space or is interpreted as a true empty string, controlled by the compatibility level setting. An empty string is interpreted as a single space if the compatibility level is less than or equal to 65,sql Server. If the compatibility level equals 70, SQL Server interprets the empty string as an empty string. For more information, see sp_dbcmptlevel.


To get information about stored procedures
To display the text used to create the procedure, execute sp_helptext in the database in which the procedure is located and use the procedure name as a parameter.





Explains that stored procedures created using the encryption option cannot be viewed using sp_helptext.



To display a report about the objects referenced by the procedure, use sp_depends.

To rename a procedure, use sp_rename.
referencing objects
SQL Server allows you to create stored procedures that reference objects that do not already exist. When created, only syntax checking is performed. When executed, the stored procedure is compiled to generate an execution plan if there is no valid schedule in the cache. All objects referenced in the stored procedure are parsed only during compilation. Therefore, if the syntactically correct stored procedure references a nonexistent object, it can still be created successfully, but will fail at run time because the referenced object does not exist. For more information, see deferred name resolution and compilation.
Deferred name resolution and compatibility levels
SQL SERVER allows Transact-SQL stored procedures to reference tables that do not exist when they are created. This ability is called deferred name resolution. However, if a Transact-SQL stored procedure references a table defined in the stored procedure, and the compatibility level setting (set by executing sp_dbcmptlevel) is 65, a warning message is issued when it is created. If the table referenced at run time does not exist, an error message is returned. For more information, see sp_dbcmptlevel and deferred name resolution and compilation.
Executing stored procedures
After the Create PROCEDURE statement is executed successfully, the procedure name is stored in the sysobjects system table, and the text of the Create PROCEDURE statement is stored in the syscomments. On first execution, the process is compiled to determine the best access plan for retrieving data.
Parameters using the cursor data type
Stored procedures can only use the cursor data type for OUTPUT parameters. If you specify a cursor data type for a parameter, you must also specify the varying and OUTPUT parameters. If you specify a varying keyword for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.





Explains that cursor data types cannot be bound to application variables through database APIs such as OLE DB, ODBC, ADO, and Db-library. Because an output parameter must be bound before an application can execute a stored procedure, a stored procedure with a cursor OUTPUT parameter cannot be invoked through the database API. These procedures can be invoked through Transact-SQL batches, stored procedures, or triggers only if the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.


Cursor Output parameters
When the procedure is executed, the following rules apply to cursor output parameters: For forward-only cursors, rows returned in the result set of the cursor are only those rows at or beyond the cursor position at the end of the execution of the stored procedure, for example: A scrollable cursor is opened on a 100-row result set named RS in the procedure.

The procedure extracts the first 5 lines of the result set RS.

The procedure returns to its caller.

The result set RS returned to the caller consists of line 6th to 100th of RS, where the cursor in the caller is before the first line of Rs. For forward-only cursors, the entire result set is returned to the calling batch, stored procedure, or trigger if the cursor is in front of the first row after the stored procedure completes. When returned, the cursor is positioned before the first row.

For forward-only cursors, empty result sets are returned for calling batches, stored procedures, or triggers if the position of the cursor exceeds the end of the last row after the stored procedure completes.


Indicates that an empty result set differs from a null value.
For scrollable cursors, all rows in the result set are returned to the calling batch, stored procedure, or trigger at the end of the execution of the stored procedure. When returned, the cursor remains where the extraction was last performed during the procedure.

For any type of cursor, if the cursor is closed, the null value is passed back to the calling batch, stored procedure, or trigger. This can also occur if a cursor is assigned to a parameter, but the cursor has never been opened.




Indicates that the shutdown state only affects when it is returned. For example, you can close a cursor in a procedure, open a cursor at a later time, and then return the cursor's result set to the calling batch, stored procedure, or trigger.


Temporary stored procedures
SQL Server supports two kinds of temporary procedures: local temporary procedures and global temporary procedures. A local temporary procedure can only be used by the connection that created the procedure. Global temporary procedures can be used by all connections. A local temporary procedure is automatically dropped at the end of the current session. The global temporary procedure is dropped at the end of the last session that uses the procedure. Typically, at the end of the session in which the procedure was created.

The temporary process is named with # and # # and can be created by any user. After the procedure is created, the owner of the local procedure is the only user who can use the procedure. Permission to perform a local temporary procedure cannot be granted to another user. If you create a global temporary procedure, all users can access the procedure, and permissions cannot be explicitly revoked. Only users with explicit create PROCEDURE permissions in the tempdb database can explicitly create temporary procedures in the database (without the use of a number character name). You can grant or revoke permissions in these procedures.





Explains that frequent use of temporary stored procedures can have a negative impact on performance by generating contention on system tables in tempdb. It is recommended to use sp_executesql instead. sp_executesql does not store data in the system tables, so this problem can be avoided.


Automatic execution of stored procedures
SQL Server can automatically execute one or more stored procedures when it starts. These stored procedures must be created by the system administrator and executed as background procedures under the sysadmin fixed server role. These procedures cannot have any input parameters.

There is no limit to the number of startup procedures, but be aware that each startup process consumes one connection at execution time. If you must execute multiple procedures at startup, but do not need to execute in parallel, you can specify a procedure to use as a startup procedure to call another procedure. This takes up only one connection.

When the last database is restored at startup, the stored procedure is started. To skip execution of these stored procedures, specify the startup parameters as trace flag 4022. If you start SQL Server with the lowest configuration (with the-f tag), the startup stored procedure is not executed. For more information, see Trace Flags.

To create a startup stored procedure, you must be logged on as a member of the sysadmin fixed server role and create a stored procedure in the master database.

You can use sp_procoption to specify an existing stored procedure as a startup process.

Stops executing the procedure when SQL Server starts.

View a list of all procedures executed at SQL Server startup. Stored Procedure Nesting
A stored procedure can be nested, that is, one stored procedure can invoke another stored procedure. When the invoked procedure starts executing, the nesting level is incremented, and the nesting level is reduced after the called procedure has finished executing. If the maximum nesting level is exceeded, the entire call process chain fails. You can use the @ @NESTLEVEL function to return the current nesting level.

To estimate the size of the compiled stored procedure, use the following performance monitoring counters.

Performance Monitor object name Performance Monitor counter name SQL Server: Buffer Manager Cache Size (number of pages) SQL Server: Cache Manager Cache Hit Rate Cache Page cache Object Count *


* These counters can be used by cache objects of various classifications, including special SQL, preparing SQL, procedures, triggers, and so on.

For more information, see SQL Server:buffer Manager object and SQL Server:cache Manager object.
Sql_statement Limit
In addition to set SHOWPLAN_TEXT and set SHOWPLAN_ALL, which must be the only statements in a batch, any set statement can be specified inside a stored procedure. The SET options you select are valid during the execution of the stored procedure and revert to the original setting.

If another user wants to use a stored procedure, within that stored procedure, the object names used by some statements must be qualified with the name of the object owner. These statements include: ALTER TABLE
CREATE INDEX
CREATE TABLE
All DBCC statements
DROP TABLE
DROP INDEX
TRUNCATE TABLE
UPDATE STATISTICS Permissions
The CREATE PROCEDURE permissions are granted to members of the sysadmin fixed server role and to the db_owner and db_ddladmin fixed database roles by default. Members of the sysadmin fixed server role and member of the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. The permission to execute the stored procedure is granted to the owner of the procedure that can set execution permissions for other database users.
Example A. Using a simple procedure with a complex SELECT statement
The following stored procedure returns all authors (names), published books, and publishers from a four-table join. The stored procedure does not use any parameters.

Use Pubsif EXISTS (SELECT name from sysobjects WHERE name = ' Au_info_all ' and type = ' P ') DROP PROCEDURE Au_info_allgocrea TE PROCEDURE au_info_allasselect au_lname, au_fname, title, pub_name from authors a INNER JOIN titleauthor ta on a.au_id = TA.AU_ID INNER join titles T on t.title_id = ta.title_id INNER Join publishers p on t.pub_id = P.pub_idgo

Au_info_all stored procedures can be performed in the following ways:

EXECUTE au_info_all--orexec Au_info_all

If the procedure is the first statement in a batch, you can use:

Au_info_all
B. Using a simple process with parameters
The following stored procedure returns only the specified author (name), published book, and publisher from a four-table join. The stored procedure accepts a value that exactly matches the parameters passed.

Use Pubsif EXISTS (SELECT name from sysobjects WHERE name = ' Au_info ' and type = ' P ') DROP PROCEDURE au_infogouse PUBSGOCR Eate PROCEDURE au_info @lastname varchar, @firstname varchar as SELECT au_lname, au_fname, title, pub_name from AU Thors a INNER join titleauthor ta on a.au_id = ta.au_id INNER join titles T on t.title_id = ta.title_id INNER Join publish ERs p on t.pub_id = p.pub_id WHERE au_fname = @firstname and au_lname = @lastnameGO

Au_info stored procedures can be performed in the following ways:

EXECUTE au_info ' dull ', ' ann '--orexecute au_info @lastname = ' dull ', @firstname = ' ann '--orexecute au_info @firstname = ' ann ', @lastname = ' dull '--orexec au_info ' dull ', ' ann '--orexec au_info @lastname = ' dull ', @firstname = ' ann '--orexec Au_info @firstname = ' Ann ', @lastname = ' dull '

If the procedure is the first statement in a batch, you can use:

Au_info ' dull ', ' ann '--orau_info @lastname = ' dull ', @firstname = ' ann '--orau_info @firstname = ' ann ', @lastname = ' dull '
C. Using simple procedures with wildcard parameters
The following stored procedure returns only the specified author (name), published book, and publisher from a four-table join. The stored procedure matches the parameters passed, and if no arguments are supplied, the default defaults are used.

Use Pubsif EXISTS (SELECT name from sysobjects WHERE name = ' Au_info2 ' and type = ' P ') DROP PROCEDURE au_info2gouse pubsgo CREATE PROCEDURE au_info2 @lastname varchar (= ' d% ', @firstname varchar () = '% ' as SELECT au_lname, au_fname, title, p Ub_namefrom authors a INNER join titleauthor ta a.au_id = ta.au_id INNER JOIN titles T on t.title_id = ta.title_id Inne R JOIN Publishers p on t.pub_id = P.pub_idwhere au_fname like @firstname and au_lname like @lastnameGO

Au_info2 stored procedures can be executed in a variety of combinations. Only some of the combinations are listed below:

EXECUTE au_info2--orexecute Au_info2 ' wh% '--orexecute au_info2 @firstname = ' a% '--orexecute au_info2 ' [ck]ars[oe]n '--O Rexecute Au_info2 ' Hunter ', ' Sheryl '--orexecute Au_info2 ' h% ', ' s% '
D. Using OUTPUT parameters
The OUTPUT parameter allows an external procedure, batch, or multiple Transact-SQL statements to access a value that is set during procedure execution. The following example creates a stored procedure (titles_sum) and uses an optional input parameter and an output parameter.

First, create the process:

Use Pubsgoif EXISTS (SELECT name from sysobjects WHERE name = ' Titles_sum ' and type = ' P ') DROP PROCEDURE titles_sumgouse P Ubsgocreate PROCEDURE titles_sum @ @TITLE varchar = '% ', @ @SUM money outputasselect ' TITLE Name ' = Titlefrom titles whe RE title like @ @TITLE SELECT @ @SUM = SUM (price) from Titleswhere title like @ @TITLEGO

Next, the OUTPUT parameter is used to control the streaming language.





Indicates that the OUTPUT variable must be defined when the table is created and the variable is used.



Parameter names and variable names do not necessarily match, but the data type and parameter positions must match (unless you use the @ @SUM = variable form).

DECLARE @ @TOTALCOST moneyexecute titles_sum ' the% ', @ @TOTALCOST outputif @ @TOTALCOST < BEGIN print ' print ' All O F These titles can is purchased for less than. Endelse SELECT ' The total cost of this titles is $ ' + RTRIM (CAST (@ @TOTALCOST as varchar (20))

Here is the result set:

Title Name------------------------------------------------------------------------The Busy Executive ' s Database Guidethe Gourmet microwavethe Psychology of Computer Cooking (3 row (s) affected) Warning, null value eliminated from Aggrega Te. All of the titles can be purchased for less than.
E. Using OUTPUT cursor parameters
The OUTPUT cursor parameter is used to pass the local cursor of the stored procedure back to the calling batch, stored procedure, or trigger.

First, you create the following procedure to declare and open a cursor on the titles table:

Use Pubsif EXISTS (SELECT name from sysobjects WHERE name = ' Titles_cursor ' and type = ' P ') DROP PROCEDURE TITLES_CURSORGOC reate PROCEDURE titles_cursor @titles_cursor cursor varying outputasset @titles_cursor = cursorforward_only STATIC Forsel ECT *from Titlesopen @titles_cursorGO

Next, execute a batch, declare a local cursor variable, perform the procedure above to assign the cursor to a local variable, and then extract the row from the cursor.

Use Pubsgodeclare @MyCursor cursorexec titles_cursor @titles_cursor = @MyCursor Outputwhile (@ @FETCH_STATUS = 0) BEGIN fetc H NEXT from @MyCursorENDCLOSE @MyCursorDEALLOCATE @MyCursorGO
F. Using the WITH RECOMPILE option
The WITH RECOMPILE clause can be helpful if the parameters provided for the procedure are not typical parameters, and the new execution plan should not be cached or stored in memory.

Use Pubsif EXISTS (SELECT name from sysobjects WHERE name = ' Titles_by_author ' and type = ' P ') DROP PROCEDURE Titles_by_au Thorgocreate PROCEDURE titles_by_author @ @LNAME_PATTERN varchar = '% ' with Recompileasselect RTRIM (au_fname) + ' + RT RIM (au_lname) as ' Authors full name ', title as Titlefrom Authors a INNER JOIN titleauthor ta on a.au_id = ta.au_id INNER J OIN titles T on ta.title_id = T.title_idwhere au_lname like @ @LNAME_PATTERNGO
G. Using the WITH ENCRYPTION option
The WITH ENCRYPTION clause hides the text of a stored procedure from the user. The following example creates the encryption process, uses the sp_helptext system stored procedure to obtain information about the encryption process, and then attempts to obtain information about the process directly from the syscomments table.

IF EXISTS (SELECT name from sysobjects WHERE name = ' Encrypt_this ' and type = ' P ') DROP PROCEDURE encrypt_thisgouse pubsgo CREATE PROCEDURE encrypt_thiswith encryptionasselect * from Authorsgoexec sp_helptext encrypt_this

Here is the result set:

The object ' s comments have been encrypted.

Next, select the identification number and text that encrypts the contents of the stored procedure.

SELECT c.id, c.text from syscomments c INNER JOIN sysobjects o on c.id = o.idwhere o.name = ' encrypt_this '

Here is the result set:





The output of the Description text column appears on a separate line. When executed, the information appears in the same row as the ID column information.



ID text----------------------------------------------------------------------1413580074??????????????????????????????? ?? E????????????????????????????????????????? (1 row (s) affected)
H. Creating a user-defined system stored procedure
The following example creates a procedure that displays all tables with the table name beginning with an EMP and their corresponding indexes. If no arguments are specified, the procedure returns all tables (and indexes) with the table name beginning with sys.

IF EXISTS (SELECT name from sysobjects WHERE name = ' sp_showindexes ' and type = ' P ') DROP PROCEDURE sp_showindexesgouse ma Stergocreate PROCEDURE sp_showindexes @ @TABLE varchar = ' sys% ' as SELECT o.name as TABLE_NAME, i.name as Index_name, in Did as Index_idfrom sysindexes i INNER JOIN sysobjects o on o.id = i.id WHERE o.name like @ @TABLEGO use pubsexec sp_showin Dexes ' emp% ' Go

Here is the result set:

table_name index_name index_id------------------------------------------------employee Employee_ind 1employee pk_ emp_id 2 (2 row (s) affected)
I. Using deferred name resolution
The following example shows the four procedures and the various possible ways to use deferred name resolution. Each stored procedure can be created, although the referenced table or column does not exist at compile time.

IF EXISTS (SELECT name from sysobjects WHERE name = ' Proc1 ' and type = ' P ') DROP PROCEDURE proc1go--Creating a PROCEDURE On a nonexistent table. Use pubsgocreate PROCEDURE proc1as SELECT * FROM Does_not_existgo-This is the statement to actually the text of the Procedure. SELECT o.id, C.textfrom sysobjects o INNER JOIN syscomments c on o.id = C.idwhere O.type = ' P ' and o.name = ' Proc1 ' gouse m Astergoif EXISTS (SELECT name from sysobjects WHERE name = ' proc2 ' and type = ' P ') DROP PROCEDURE proc2go--Creating a Pro Cedure that attempts to retrieve information from a--nonexistent column in a existing table. Use pubsgocreate PROCEDURE proc2as DECLARE @middle_init char (1) SET @middle_init = NULL SELECT au_id, middle_initial = @mi Ddle_init from authorsGO-This is the statement to actually the text of the procedure. SELECT o.id, C.textfrom sysobjects o INNER JOIN syscomments c on o.id = C.idwhere O.type = ' P ' and o.name = ' proc2 ' to be transferred from: HTT p://goaler.xicp.net/showlog.asp?id=515