SQL Server database Stored Procedures

1. What is a stored procedure?

Stored Procedures (Stored Procedure) are a set of SQL statements for specific functions in large database systems. They are compiled and stored in the database, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters.

2. What are the advantages and disadvantages of stored procedures?

Advantages:

The stored procedure capability greatly enhances the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation.

This ensures data security and integrity.

Through the stored procedure, users without permissions can indirectly access the database under control, thus ensuring data security.

Through the stored procedure, related actions can be taken together to maintain the integrity of the database. Disadvantage: debugging is troublesome, but it is very convenient to debug with PL/SQL developer! Make up for this shortcoming. The database code is of course related to the database. However, for engineering projects, there is basically no porting problem. Re-compilation problem, because the back-end code is compiled before running, if the object with a reference relationship changes, the affected stored procedures and packages need to be re-compiled (but you can also set it to automatically compile at runtime ). If you use a large number of stored procedures in a program system, the changes in the data structure will occur as the user needs increase, followed by system problems, finally, if you want to maintain the system, it can be said that it is difficult, and the cost is unprecedented, making maintenance more troublesome.

 

3. Simple format of Stored Procedures

Create procedure [owner.] stored procedure name [; program No.] stored procedure in SQL and related Introduction [(parameter #1 ,... Parameter #1024)] [With {recompile | encryption | recompile, encryption}] [for replication] As program line where the stored procedure name cannot exceed 128 words. A maximum of 1024 parameters can be set in each stored procedure (SQL Server 7.0 or later). The parameter usage is as follows: @ Parameter Name Data Type [varying] [= internal value] [Output] each parameter name must have a "@" symbol before it. The parameters of each stored procedure are only used inside the program, the parameter type is applicable to data types supported by SQL Server except image. [= Internal value] is equivalent to setting the default value of a field when creating a database. Here we set the default value for this parameter. [Output] is used to specify that this parameter has both input and output values. That is, when the stored procedure is called, if the specified parameter value is the parameter we need to enter, if the parameter must be output in the result, it must be output. If the parameter is used only for output, you can use cursor, the varying and output statements must be specified.

 

----- User-Defined Stored Procedure

1. Create a syntax

Create proc | procedure pro_name
[{@ Parameter data type} [= default value] [Output],
{@ Parameter data type} [= default value] [Output],
....
]
As
SQL _statements

2. Create a stored procedure without Parameters

-- Create a stored procedure
If (exists (select * From SYS. objects where name = 'proc _ get_student '))
Drop proc proc_get_student
Go
Create proc proc_get_student
As
Select * from student;

-- Call and execute stored procedures
Exec proc_get_student;

3. Modify the Stored Procedure

-- Modify the Stored Procedure
Alter proc proc_get_student
As
Select * from student;

4. Stored Procedure with Parameters

-- Stored procedure with Parameters
If (object_id ('proc _ find_stu ', 'P') is not null)
Drop proc proc_find_stu
Go
Create proc proc_find_stu (@ startid int, @ endid INT)
As
Select * from student where ID between @ startid and @ endid
Go

Exec proc_find_stu 2, 4;

5. Stored Procedures with wildcard Parameters

-- Stored procedure with wildcard Parameters
If (object_id ('proc _ findstudentbyname', 'P') is not null)
Drop proc proc_findstudentbyname
Go
Create proc proc_findstudentbyname (@ name varchar (20) = '% J %', @ nextname varchar (20) = '% ')
As
Select * from student where name like @ name and name like @ nextname;
Go

Exec proc_findstudentbyname;
Exec proc_findstudentbyname '% o %','t % ';

6. Stored Procedure with output parameters

If (object_id ('proc _ getstudentrecord ', 'P') is not null)
Drop proc proc_getstudentrecord
Go
Create proc proc_getstudentrecord (
@ ID int, -- default input parameter
@ Name varchar (20) Out, -- output parameter
@ Age varchar (20) Output -- Input and Output Parameters
)
As
Select @ name = Name, @ age = age from student where id = @ ID and sex = @ age;
Go

--
Declare @ ID int,
@ Name varchar (20 ),
@ Temp varchar (20 );
Set @ ID = 7;
Set @ temp = 1;
Exec proc_getstudentrecord @ ID, @ name out, @ temp output;
Select @ name, @ temp;
Print @ name + '#' + @ temp;

 

 

7. Non-Cache stored procedures

-- With recompile is not cached
If (object_id ('proc _ temp ', 'P') is not null)
Drop proc proc_temp
Go
Create proc proc_temp
With recompile
As
Select * from student;
Go

Exec proc_temp;

8. Encrypted storage process

-- Encryption with encryption
If (object_id ('proc _ temp_encryption ', 'P') is not null)
Drop proc proc_temp_encryption
Go
Create proc proc_temp_encryption
With Encryption
As
Select * from student;
Go

Exec proc_temp_encryption;
Exec sp_helptext 'proc _ temp ';
Exec sp_helptext 'proc _ temp_encryption ';

9. Stored Procedures with cursor Parameters

If (object_id ('proc _ cursor ', 'P') is not null)
Drop proc proc_cursor
Go
Create proc proc_cursor
@ Cur cursor varying output
As
Set @ cur = cursor forward_only static
Select ID, name, age from student;
Open @ cur;
Go
-- Call
Declare @ exec_cur cursor;
Declare @ ID int,
@ Name varchar (20 ),
@ Age int;
Exec proc_cursor @ cur = @ exec_cur output; -- call the Stored Procedure
Fetch next from @ exec_cur into @ ID, @ name, @ age;
While (@ fetch_status = 0)
Begin
Fetch next from @ exec_cur into @ ID, @ name, @ age;
Print 'id: '+ convert (varchar, @ ID) +', name: '+ @ name +', age: '+ convert (char, @ age );
End
Close @ exec_cur;
Deallocate @ exec_cur; -- delete a cursor

 

 

10. Paging Stored Procedure

--- Stored procedure and row_number complete Paging
If (object_id ('Pro _ page', 'P') is not null)
Drop proc proc_cursor
Go
Create proc pro_page
@ Startindex int,
@ Endindex int
As
Select count (*) from product
;
Select * from (
Select row_number () over (order by PID) as rowid, * from product
) Temp
Where temp. rowid between @ startindex and @ endindex
Go
-- Drop proc pro_page
Exec pro_page 1, 4
--
-- Paging Stored Procedure
If (object_id ('Pro _ page', 'P') is not null)
Drop proc pro_stu
Go
Create procedure pro_stu (
@ Pageindex int,
@ Pagesize int
)
As
Declare @ startrow int, @ endrow int
Set @ startrow = (@ pageindex-1) * @ pagesize + 1
Set @ endrow = @ startrow + @ pagesize-1
Select * from (
Select *, row_number () over (order by id asc) as number from student
) T
Where T. number between @ startrow and @ endrow;

Exec pro_stu 2, 2;

 

 

----- Raiserror

Raiserror returns the User-Defined error message. You can specify the Severity Level and set the system variable record errors.

Syntax:

Raiserror ({msg_id | msg_str | @ local_variable}
{, Severity, State}
[, Argument [,... N]
[With option [,... N]
)

# Msg_id: User-Defined error information specified in the sysmessages system table

# Msg_str: User-defined information. The maximum length of the information is 2047 characters.

# Severity: The severity level associated with the message. When msg_id is used to trigger a user-defined message created using sp_addmessage, the specified severity on raiserror overwrites the severity defined in sp_addmessage.

Any user can specify 0-18 direct severity levels. Only users frequently used by SysAdmin fixed server roles or having the alter trace permission can specify a direct Severity Level. The with log option is required for security levels between and.

# State: any integer between 1 and 127. The default value of state is 1.

Raiserror ('is error', 16, 1 );
Select * From SYS. messages;
-- Use the message defined in sysmessages
Raiserror (33003, 16, 1 );
Raiserror (33006, 16, 1 );