In depth analysis of SQL server stored procedures


The stored procedure in transact SQL is very similar to the method in Java language. It can be called repeatedly. When a stored procedure is executed once, the statement can be cached, so that the next time the stored procedure is executed, the statement in the cache can be used directly. This can improve the performance of stored procedures.

The concept of stored procedure

Stored procedure is a set of SQL statements to complete specific functions. It is compiled and stored in the database. The user can execute it by specifying the name of the stored procedure and giving parameters.

Stored procedure can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets and return values.

Because the stored procedure is compiled on the database server and stored in the database when it is created, it runs faster than a single SQL statement block. At the same time, because only the stored procedure name and the necessary parameter information need to be provided when calling, it can also reduce the network traffic and simple network burden to a certain extent.

1. Advantages of stored procedures

A. Stored procedure allows standard component programming

After the stored procedure is created, it can be called and executed many times in the program without rewriting the SQL statement of the stored procedure. And database professionals can modify stored procedures at any time, but it has no impact on the application source code, which greatly improves the portability of the program.

B. Stored procedure can achieve faster execution speed

If an operation contains a lot of T-SQL statement code and is executed several times, then the execution speed of stored procedure is much faster than that of batch processing. Because a stored procedure is precompiled, the query optimizer analyzes and optimizes a stored procedure for the first time, and gives the storage plan that is finally stored in the system table. Batch T-SQL statements need to be precompiled and optimized every time they run, so the speed is slower.

C. Stored procedures reduce network traffic

For the same operation on database object, if the T-SQL statement involved in this operation is organized into a stored procedure, when the stored procedure is called on the client, only the calling statement is passed in the network, otherwise it will be multiple SQL statements. Thus, the network traffic is reduced and the network load is reduced.

D. Stored procedure can be fully utilized as a security mechanism

The system administrator can restrict the authority of a stored procedure, so as to restrict the access to some data, avoid the access to data by unauthorized users, and ensure the security of data.

System stored procedure

System stored procedure is a stored procedure created by the system, which aims to easily query information from the system table or complete the management tasks related to updating the database table or other system management tasks. The system stored procedure is mainly stored in the master database, which starts with the “SP” underline. Although these system stored procedures are in the master database, we can still call them in other databases. Some system stored procedures will be automatically created in the current database when creating a new database.

Common system stored procedures are as follows:

Copy codeThe code is as follows:
exec sp_ Databases; — View databases
exec sp_ Tables; — view tables
exec sp_ Columns student; — View columns
exec sp_ HelpIndex student; — view index
exec sp_ Helpconstraint student; — constraint
exec sp_stored_procedures;
exec sp_ helptext ‘sp_ stored_ Procedures’; — view the creation and definition statements of stored procedures
exec sp_ Rename student, stuInfo; — modify the names of tables, indexes, and columns
exec sp_ Rename dB, mytempdb, mydb; — change database name
exec sp_ Defaultdb ‘master’,’mydb ‘; — change the default database of login name
exec sp_ Helpdb; — database help, query database information
exec sp_helpdb master;

System stored procedure example:
–Table rename

exec sp_rename 'stu', 'stud';
select * from stud;

–Column rename

exec sp_rename '', 'sName', 'column';
exec sp_help 'stud';

–Rename index

exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';

–Query all stored procedures

select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

User defined stored procedure

1. Create syntax

create proc | procedure pro_name
[{@ parameter data type} [= Default] [output],
{@ parameter data type} [= Default] [output],

2. Creating stored procedures without parameters

–Create stored procedure

if (exists (select * from sys.objects where name = 'proc_get_student'))
  drop proc proc_get_student
create proc proc_get_student
  select * from student;

–Calling and executing stored procedure

exec proc_get_student;

3. Modifying stored procedures

–Modifying stored procedures

alter proc proc_get_student
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
create proc proc_find_stu(@startId int, @endId int)
  select * from student where id between @startId and @endId

exec proc_find_stu 2, 4;

5. Stored procedure with wildcard parameters

–Stored procedure with wildcard parameters

if (object_id('proc_findStudentByName', 'P') is not null)
  drop proc proc_findStudentByName
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
  select * from student where name like @name and name like @nextName;

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
create proc proc_getStudentRecord(
  @Id int, -- default input parameter
  @Name varchar (20) out, - output parameter
  @Age varchar (20) output -- input and output parameters
  select @name = name, @age = age from student where id = @id and sex = @age;

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. Do not cache stored procedures

–With resume does not cache

if (object_id('proc_temp', 'P') is not null)
  drop proc proc_temp
create proc proc_temp
with recompile
  select * from student;

exec proc_temp;

8. Encrypted stored procedure

–Encryption with encryption

if (object_id('proc_temp_encryption', 'P') is not null)
  drop proc proc_temp_encryption
create proc proc_temp_encryption
with encryption
  select * from student;

exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';

9. Stored procedure with cursor parameters

if (object_id('proc_cursor', 'P') is not null)
  drop proc proc_cursor
create proc proc_cursor
  @cur cursor varying output
  set @cur = cursor forward_only static for
  select id, name, age from student;
  open @cur;
declare @exec_cur cursor;
declare @id int,
    @name varchar(20),
    @age int;
exec proc_cursor @cur = @exec_cur output;--Call存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
  fetch next from @exec_cur into @id, @name, @age;
  print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
close @exec_cur;
deallocate @exec_ Cur; -- delete cursor

10. Paging stored procedure

—Stored procedure, row_ Number to complete paging

if (object_id('pro_page', 'P') is not null)
  drop proc proc_cursor
create proc pro_page
  @startIndex int,
  @endIndex int
  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
--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
create procedure pro_stu(
  @pageIndex int,
  @pageSize int
  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 information. It can specify the severity level and set the system variable to record the error.

The syntax is as follows:

Raiserror({msg_id | msg_str | @local_variable}
  {, severity, state}
  [with option[,…n]]
   # msg_ ID: user defined error message specified in sysmessages system table

# msg_ STR: user defined information. The maximum length of information is 2047 characters.

#Severity: user defined severity level associated with the message. When using MSG_ ID raises the use of sp_ When addmessage creates a user-defined message, the specified severity on raiserrror will override sp_ The severity defined in addmessage.

Any user can specify a 0-18 direct severity level. Only users who are commonly used in the sysadmin fixed server role or have the alter trace privilege can specify a 19-25 direct severity level. Security levels between 19 and 25 require the with log option.

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

raiserror(‘is error’, 16, 1);
select * from sys.messages;
–Using messages defined in sysmessages
raiserror(33003, 16, 1);
raiserror(33006, 16, 1);

The above is the whole content of this article, I hope you can like it.