How to Write stored procedures in SQL Server

I always want to know how to write a stored procedure. I always feel mysterious about unfamiliar things. Especially when looking at the stored procedures written by students, it seems hard to understand because they do not understand the basic syntax of the stored procedures. Until today, I finally opened the SQL Server books online and learned some of its basic usage. The following are some excerpts. I hope that I can learn more and better understand them later.

1. design the Stored Procedure

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

2. design rules for Stored Procedures

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.

3. specify the name of the 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 a table.MarytabIf she wants other users to execute the stored procedure using the table, the table name must be limited when the table is used in 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 not specifiedMarytabWhile John tries to execute this process, SQL Server will findMarytab.

4. 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.

5. 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, createPubsUseful stored procedures in databases. 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) asselect au_lname, au_fname, title, pub_namefrom authors inner join titleauthor on authors. au_id = titleauthor. au_idjoin titles on titleauthor. title_id = titles. title_idjoin publishers on titles. pub_id = publishers. pub_idwhere au_fname = @ firstnameand au_lname = @ lastnamego
 

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

Run nowAu_infoStored Procedure:

 
Execute au_info ringer, annego
 

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

In the following example, create a stored procedurePub_info2The stored procedure 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' asselect au_lname, au_fname, pub_namefrom authors a inner join titleauthor Ta on. au_id = TA. au_idjoin titles t on TA. title_id = T. title_idjoin publishers P on t. pub_id = P. pub_idwhere @ pubname = P. pub_name
 

RunPub_info2:

 
Execute pub_info2go
 

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 stored procedureShowind2Of@ TableThe default value isTitles.

 
Create proc showind2 @ table varchar (30) = 'title' asselect table_name = sysobjects. name, index_name = sysindexes. name, index_id = indidfrom sysindexes inner join sysobjects on sysobjects. id = sysindexes. idwhere sysobjects. name = @ table
 

Column Title (for example,Table_name) To make the results more readable. The following figure shows the Stored ProcedureAuthorsTable:

Execute showind2 authorsgo
 

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 tableTitles:

 
Execute showind2go
 

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) = nullas if @ table is nullprint 'give a table name' elseselect table_name = sysobjects. name, index_name = sysindexes. name, index_id = indidfrom sysindexes inner join sysobjectson sysobjects. id = sysindexes. idwhere 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 exampleShowindModify to display information about the system table when no parameters are provided:

 
Create proc showind4 @ table varchar (30) = 'sys % 'as select table_name = sysobjects. name, index_name = sysindexes. name, index_id = indidfrom sysindexes inner join sysobjectson sysobjects. id = sysindexes. idwhere sysobjects. name like @ table
 

In the Stored ProcedureAu_infoIn the following form, both parameters have default values with wildcards:

Create proc au_info2 @ lastname varchar (30) = 'd % ', @ firstname varchar (18) =' % 'asselect au_lname, au_fname, title, pub_namefrom authors inner join titleauthor on authors. au_id = titleauthor. au_idjoin titles on titleauthor. title_id = titles. title_idjoin publishers on titles. pub_id = publishers. pub_idwhere au_fname like @ firstnameand au_lname like @ lastname
 

IfAu_info2If no parameter is specified, all authors whose names start with the letter D are displayed:

 
Execute au_info2go
 

The following is the result set:

& Lt; TD width = "20%" & gt; au_lname & lt;/TD & gt; & Lt; TD width = "14%" & gt; au_fname & lt;/TD & gt; & Lt; TD width = "31%" & gt; pub_name & lt;/TD & gt; & Lt; TD width = "20%" & gt; dull & lt;/TD & gt; & Lt; TD width = "14%" & gt; Innes & lt;/TD & gt; & Lt; TD width = "20%" & gt; defrance & lt;/TD & gt;

title
--------	--------	---------------------	-----------------
Ann	secrets of silicon Val	algodata infosystems
Del Castillo	Silicon Val gastrono	binnet & hardley
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.RingerAll authors and publishers:

 
Execute au_info2 ringergo
 

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)