Design the Stored Procedure

Almost any Transact-SQL statement that can be written in batches Code Can be used to create a stored procedure.

Stored Procedure Design Rules
Stored Procedure Design Rules include:

The create procedure definition can include any number and types of SQL statements except the following create statements. The following statements cannot be used anywhere in the stored procedure: create default create trigger
Create procedure create View
Create rule

You can create other database objects in the stored procedure. Objects Created in the same stored procedure can be referenced only after an object is created.

You can reference a temporary table in a stored procedure.

If you create a local temporary table in a stored procedure, the temporary table exists only for the stored procedure. After you exit the stored procedure, the temporary table disappears.

If you execute a stored procedure that calls other stored procedures, the called stored procedure can access all objects created by the first stored procedure, including temporary tables.

If you execute a change stored procedure on a remote Microsoft SQL Server 2000 instance, these changes cannot be rolled back. Remote stored procedures are not involved in transaction processing.

The maximum number of parameters in a stored procedure is 2100.

The maximum number of local variables in a stored procedure is limited by the available memory.

Depending on the available memory, the maximum size of the stored procedure can be 128 MB.
For more information about the rules for creating stored procedures, see create procedure.

Specifies the name of a stored procedure.
In a stored procedure, if the object name used for statements such as select or insert is not user-defined, the user is the owner of the stored procedure by default. In a stored procedure, if the user who creates the stored procedure does not limit the table names referenced in the select, insert, update, or delete statements, by default, access to these tables through the stored procedure is restricted by the Creator permission of the process.

If other users want to use the stored procedure, the names of objects used for statement alter table, create table, drop table, truncate table, create index, drop index, update statistics, and DBCC must be limited by the name of the object owner. For example, Mary owns the table marytab. If she wants other users to execute the stored procedure using the table, the table name must be limited when the table is used for one of the preceding statements.

This rule is required because the name of the object will be parsed when the stored procedure is run. If the marytab is not limited and John tries to execute this process, SQL Server searches for the table named marytab owned by John.

Encryption Process Definition
If you want to create a stored procedure and make sure that other users cannot view the definition of the procedure, you can use the with encryption clause. In this way, the process definition is stored as unreadable.

Once a stored procedure definition is encrypted, it cannot be decrypted. No one (including the owner or system administrator of the stored procedure) can view the stored procedure definition.

Set statement options
When ODBC ApplicationProgramWhen you connect to SQL server, the server automatically sets the following options for sessions:

Set quoted_identifier on

Set textsize 2147483647

Set ansi_defaults on

Set cursor_close_on_commit off

Set implicit_transactions off
These settings increase the portability of ODBC applications. Because DB-library-based applications do not usually set these options, you should test the stored procedure when the set options listed above are enabled or disabled. This ensures that the stored procedure always works correctly, regardless of the options that may be set when a specific connection wakes up and calls the stored procedure. If you need to set a stored procedure for one of the options, you should issue a set statement when starting the stored procedure. This set statement will only be valid for execution of the stored procedure. When the stored procedure ends, the original setting will be restored.

Example
A. Create a stored procedure using parameters
In the following example, create a stored procedure that is useful in the pubs database. The last name and name of an author. The stored procedure displays the title and publisher of each book of the author.

Create proc au_info @ lastname varchar (40), @ firstname varchar (20)
As
Select au_lname, au_fname, title, pub_name
From authors inner join titleauthor on authors. au_id = titleauthor. au_id
Join titles on titleauthor. title_id = titles. title_id
Join publishers on titles. pub_id = publishers. pub_id
Where au_fname = @ firstname
And au_lname = @ lastname
Go

A message indicating that the command does not return any data or any rows will appear, indicating that the stored procedure has been created.

Now execute the au_info stored procedure:

Execute au_info ringer, Anne
Go

The following is the result set:

Au_lname au_fname title pub_name
-------------------------------------------------------
Ringer Anne the gourmet microwave binnet & hardley
Ringer Anne is anger the enemy? New Moon books

(2 row (s) affected)

B. Create a stored procedure using the default parameter values
The following example creates a stored procedure pub_info2, which displays the name of the author of a book published by the publisher as a parameter. If the publisher name is not provided, the stored procedure displays the author of the book published by algodata infosystems.

Create proc pub_info2 @ pubname varchar (40) = 'algodata infosystem'
As
Select au_lname, au_fname, pub_name
From authors a inner join titleauthor Ta on A. au_id = TA. au_id
Join titles t on TA. title_id = T. title_id
Join publishers P on T. pub_id = P. pub_id
Where @ pubname = P. pub_name

Run pub_info2 with unspecified parameters:

Execute pub_info2
Go

The following is the result set:

Au_lname au_fname pub_name
----------------------------------------------------
Green Marjorie algodata infosystems
Bennet Abraham algodata infosystems
O 'Leary Michael algodata infosystems
Macfeather Stearns algodata infosystems
Straight Dean algodata infosystems
Carson Cheryl algodata infosystems
Dull Ann algodata infosystems
Hunter Sheryl algodata infosystems
Locksley Charlene algodata infosystems

(9 row (s) affected)

C. Execute the stored procedure that replaces the default value of the parameter with an explicit Value
In the following example, the default value of @ table in showind2 is titles.

Create proc showind2 @ table varchar (30) = 'tidles'
As
Select table_name = sysobjects. Name,
Index_name = sysindexes. Name, index_id = indid
From sysindexes inner join sysobjects on sysobjects. ID = sysindexes. ID
Where sysobjects. Name = @ table

The column title (for example, table_name) can make the results more readable. The following table lists the authors tables displayed in the stored procedure:

Execute showind2 authors
Go

Table_name index_name index_id
------------------------------
Authors upkcl_auidind 1
Authors aunmind 2

(2 row (s) affected)

If the user does not provide a value, SQL server uses the default table titles:

Execute showind2
Go

The following is the result set:

Table_name index_name index_id
------------------------------
Titles upkcl_titleidind 1
Titles titleind 2

(2 row (s) affected)

D. Create a stored procedure using the default value null.
The default value can be null. In this case, if no parameter is provided, SQL Server executes the stored procedure based on other statements in the stored procedure. No error message is displayed.

The process definition can also specify other measures to be taken when no parameter is given. For example:

Create proc showind3 @ table varchar (30) = NULL
As if @ table is null
Print 'give a table name'
Else
Select table_name = sysobjects. Name,
Index_name = sysindexes. Name, index_id = indid
From sysindexes inner join sysobjects
On sysobjects. ID = sysindexes. ID
Where sysobjects. Name = @ table

E. Create a stored procedure using the default value of a parameter that contains wildcards
If the stored procedure uses the like keyword, the default value can include wildcards (%, _, [], and [^]). For example, you can modify showind to display system table information when no parameters are provided:

Create proc showind4 @ table varchar (30) = 'sys %'
As select table_name = sysobjects. Name,
Index_name = sysindexes. Name, index_id = indid
From sysindexes inner join sysobjects
On sysobjects. ID = sysindexes. ID
Where sysobjects. name like @ table

In the following change form of the stored procedure au_info, both parameters have the default values with wildcards:

Create proc au_info2 @ lastname varchar (30) = 'd % ',
@ Firstname varchar (18) = '%'
As
Select au_lname, au_fname, title, pub_name
From authors inner join titleauthor on authors. au_id = titleauthor. au_id
Join titles on titleauthor. title_id = titles. title_id
Join publishers on titles. pub_id = publishers. pub_id
Where au_fname like @ firstname
And au_lname like @ lastname

If you do not specify a parameter when executing au_info2, all authors whose names start with the letter D will be displayed:

Execute au_info2
Go

The following is the result set:

Au_lname au_fname title pub_name
--------------------------------------------------------
Dull Ann secrets of silicon Val algodata infosystems
Del Castillo Innes silicon Val gastrono binnet & hardley
Defrance micel the gourmet microwave binnet & hardley

(3 row (s) affected)

In the following example, the second parameter is omitted when the default values of the two parameters are defined. Therefore, you can find all the authors and publishers whose names are ringer:

Execute au_info2 ringer
Go

Au_lname au_fname title pub_name
--------------------------------------------------------
Ringer Anne the gourmet microwave binnet & hardley
Ringer Anne is anger the enemy? New Moon books
Ringer Albert is anger the enemy? New Moon books
Ringer Albert life without fear new moon books

(4 row (s) affected)