In depth analysis of SQL server stored procedures

Time:2021-4-17

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 'stud.name', '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],
     ….
    ]
as
    SQL_statements

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
go
create proc proc_get_student
as
  select * from student;

–Calling and executing stored procedure


exec proc_get_student;

3. Modifying stored procedures

–Modifying stored procedures


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

–With resume does not cache


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 stored procedure

–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 procedure 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 for
  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存储过程
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 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
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 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}
  [,argument[,…n]]
  [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.