Stored Procedure example

A. Simple process with complex select statements
The following stored procedure returns all authors (names provided), published books, and publishers from the join of the four tables. This stored procedure does not use any parameters.

Use pubs
If exists (Select name from sysobjects
Where name = 'au _ info_all 'and type = 'P ')
Drop procedure au_info_all
Go
Create procedure au_info_all
As
Select 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_id
Go

The au_info_all stored procedure can be executed in the following ways:

Execute au_info_all
-- Or
Exec au_info_all

If the process is the first statement in batch processing, you can use:

Au_info_all

B. Simple process with Parameters
The following stored procedure returns only the specified author (name provided), published books, and publishing house from the join of the four tables. The stored procedure accepts values exactly matching the passed parameters.

Use pubs
If exists (Select name from sysobjects
Where name = 'au _ info' and type = 'P ')
Drop procedure au_info
Go
Use pubs
Go
Create procedure au_info
@ Lastname varchar (40 ),
@ Firstname varchar (20)
As
Select 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_id
Where au_fname = @ firstname
And au_lname = @ lastname
Go

The au_info stored procedure can be executed in the following ways:

Execute au_info 'dull ', 'ann'
-- Or
Execute au_info @ lastname = 'dull ', @ firstname = 'ann'
-- Or
Execute au_info @ firstname = 'ann ', @ lastname = 'dull'
-- Or
Exec au_info 'dull ', 'ann'
-- Or
Exec au_info @ lastname = 'dull ', @ firstname = 'ann'
-- Or
Exec au_info @ firstname = 'ann ', @ lastname = 'dull'

If the process is the first statement in batch processing, you can use:

Au_info 'dull', 'ann'
-- Or
Au_info @ lastname = 'dull ', @ firstname = 'ann'
-- Or
Au_info @ firstname = 'ann ', @ lastname = 'dull'

C. simple process of using parameters with wildcards
The following stored procedure returns only the specified author (name provided), published books, and publishing house from the join of the four tables. This stored procedure matches the pattern of the passed parameters. If no parameter is provided, the default value is used.

Use pubs
If exists (Select name from sysobjects
Where name = 'au _ info2 'and type = 'P ')
Drop procedure au_info2
Go
Use pubs
Go
Create procedure au_info2
@ Lastname varchar (30) = 'd % ',
@ Firstname varchar (18) = '%'
As
Select 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_id
Where au_fname like @ firstname
And au_lname like @ lastname
Go

The au_info2 stored procedure can be executed in multiple combinations. Only some combinations are listed below:

Execute au_info2
-- Or
Execute au_info2 'wh %'
-- Or
Execute au_info2 @ firstname = 'a %'
-- Or
Execute au_info2 '[Ck] ARS [OE] N'
-- Or
Execute au_info2 'hunter', 'sheryl'
-- Or
Execute au_info2 'H % ','s %'

D. Use output parameters
The output parameter allows external processes, batch processing, or multiple Transact-SQL statements to access a value set during execution of the process. The following example creates a stored procedure (titles_sum) and uses an optional input parameter and an output parameter.

First, the creation process:

Use pubs
Go
If exists (Select name from sysobjects
Where name = 'titles _ Sum' and type = 'P ')
Drop procedure titles_sum
Go
Use pubs
Go
Create procedure titles_sum @ title varchar (40) = '%', @ sum money output
As
Select 'title name' = title
From titles
Where title like @ title
Select @ sum = sum (price)
From titles
Where title like @ title
Go

The output parameter is used in the control flow language.

The output variable must be defined when the table is created and used.

The parameter name and variable name do not have to match, but the data type and parameter location must match (unless in the form of @ sum = variable ).

Declare @ totalcost money
Execute titles_sum 'the % ', @ totalcost output
If @ totalcost <200
Begin
Print''
Print 'All of these titles can be purchased for less than $200 .'
End
Else
Select 'the total cost of these titles is $'
+ Rtrim (cast (@ totalcost as varchar (20 )))

The following is the result set:

Title name
------------------------------------------------------------------------
The busy executive's database Guide
The Gourmet microwave
The psychology of computer cooking

(3 row (s) affected)

Warning, null value eliminated from aggregate.
 
All of these titles can be purchased for less than $200.

E. Use output cursor Parameters
The output cursor parameter is used to pass the partial cursor of a stored procedure back to call a batch, stored procedure, or trigger.

First, create the following process and declare and open a cursor on the titles table:

Use pubs
If exists (Select name from sysobjects
Where name = 'titles _ cursor 'and type = 'P ')
Drop procedure titles_cursor
Go
Create procedure titles_cursor @ titles_cursor cursor varying output
As
Set @ titles_cursor = cursor
Forward_only static
Select *
From titles

Open @ titles_cursor
Go

Next, execute a batch process, declare a local cursor variable, execute the above process to assign the cursor to the local variable, and then extract rows from the cursor.

Use pubs
Go
Declare @ mycursor cursor
Exec titles_cursor @ titles_cursor = @ mycursor output
While (@ fetch_status = 0)
Begin
Fetch next from @ mycursor
End
Close @ mycursor
Deallocate @ mycursor
Go

F. Use the with recompile Option
If the parameters provided for the process are not a typical parameter and the new execution plan should not be cached or stored in memory, the with recompile clause will be helpful.

Use pubs
If exists (Select name from sysobjects
Where name = 'titles _ by_author 'and type = 'P ')
Drop procedure titles_by_author
Go
Create procedure titles_by_author @ lname_pattern varchar (30) = '%'
With recompile
As
Select rtrim (au_fname) + ''+ rtrim (au_lname) as 'authors full name ',
Title as title
From authors a inner join titleauthor Ta
On a. au_id = TA. au_id inner join titles t
On ta. title_id = T. title_id
Where au_lname like @ lname_pattern
Go

G. Use the with encryption option
The with encryption clause hides the Stored Procedure text from the user. In the following example, create an encryption process and use the sp_helptext system stored procedure to obtain information about the encryption process. Then, try to obtain information about the process directly from the syscomments table.

If exists (Select name from sysobjects
Where name = 'encryption _ this 'and type = 'P ')
Drop procedure encrypt_this
Go
Use pubs
Go
Create procedure encrypt_this
With Encryption
As
Select *
From authors
Go

Exec sp_helptext encrypt_this

The following is the result set:

The object's comments have been encrypted.

Next, select the ID and text of the stored procedure content.

Select C. ID, C. Text
From syscomments C inner join sysobjects o
On C. ID = O. ID
Where o. Name = 'encrypt _ this'

The following is the result set:

The output of the text column is displayed in a single row. This information appears in the same row as the ID column during execution.

Id text
----------------------------------------------------------------------
1413580074 ????????????????????????????????? E ??????????????????????????????????????? ???????????????????????????????????

(1 row (s) affected)

H. Create a user-defined system stored procedure
The following example creates a process that displays all tables whose names start with EMP and their corresponding indexes. If no parameter is specified, this process returns all tables (and indexes) whose names start with sys ).

If exists (Select name from sysobjects
Where name = 'SP _ showindexes' and type = 'P ')
Drop procedure sp_showindexes
Go
Use master
Go
Create procedure sp_showindexes
@ Table varchar (30) = 'sys %'
As
Select O. Name as table_name,
I. Name as index_name,
Indid as index_id
From sysindexes I inner join sysobjects o
On O. ID = I. ID
Where o. name like @ table
Go
Use pubs
Exec sp_showindexes 'emp' %'
Go

The following is the result set:

Table_name index_name index_id
------------------------------------------------
Employee employee_ind 1
Employee pk_emp_id 2

(2 row (s) affected)

I. Use delay name resolution
The following example shows the four processes and various possible use modes for delayed name resolution. Although the referenced table or column does not exist during compilation, each stored procedure can be created.

If exists (Select name from sysobjects
Where name = 'proc1 'and type = 'P ')
Drop procedure proc1
Go
-- Creating a procedure on a nonexistent table.
Use pubs
Go
Create procedure proc1
As
Select *
From does_not_exist
Go
-- Here is the statement to actually see the text of the procedure.
Select O. ID, C. Text
From sysobjects o inner join syscomments C
On O. ID = C. ID
Where o. type = 'p' and O. Name = 'proc1'
Go
Use master
Go
If exists (Select name from sysobjects
Where name = 'proc2 'and type = 'P ')
Drop procedure proc2
Go
-- Creating a procedure that attempts to retrieve information from
-- Nonexistent column in an existing table.
Use pubs
Go
Create procedure proc2
As
Declare @ middle_init char (1)
Set @ middle_init = NULL
Select au_id, middle_initial = @ middle_init
From authors
Go
-- Here is the statement to actually see the text of the procedure.
Select O. ID, C. Text
From sysobjects o inner join syscomments C
On O. ID = C. ID
Where o. type = 'p' and O. Name = 'proc2'