How to Write stored procedures in SQL Server

Source: Internet
Author: User
Tags sql server books
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)






Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.