In-depth analysis of SQL Server stored procedures _mssql

A stored procedure in Transact-SQL, very similar to a method in the Java language, that can be called repeatedly. Once the stored procedure is executed once, the statement can be cached so that the statement in the cache is used directly the next time it executes. This will improve the performance of the stored procedure.

Ø The concept of stored procedures

Stored procedure procedure is a set of SQL statements that are compiled and stored in a database to complete a specific function, and are executed by specifying the name of the stored procedure and giving the parameters.

Stored procedures 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 stored procedures are compiled on the database server and stored in a database when they are created, stored procedures run faster than a single block of SQL statements. At the same time, it can reduce network traffic and simple network burden to a certain extent because it only needs to provide the stored procedure name and the necessary parameter information in the call.

1, the advantages of stored procedures

A, stored procedures allow standard component-type programming

After a stored procedure is created, it can be executed multiple times in a program without having to rewrite the SQL statement of the stored procedure. and database professionals can modify the stored procedures at any time, but have no effect on the application source code, thereby greatly improving the portability of the program.

B, the stored procedure can achieve faster execution speed

If an action contains a large number of T-SQL statement code that is executed multiple times, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, the query optimizer analyzes, optimizes, and gives a storage plan in the system table that is ultimately present when a stored procedure is first run. The T-SQL statements for batches need to be precompiled and optimized each time, so the speed is slower.

C, stored procedures to reduce network traffic

For the same operation against a database object, if the T-SQL statement involved in the operation is organized into a stored procedure, when the stored procedure is invoked on the client, the network is passed only by the calling statement, otherwise it will be more than one SQL statement. This reduces network traffic and lowers Network load.

D, stored procedures can be used as a security mechanism to fully utilize

A system administrator can restrict the execution of one stored procedure, thereby restricting access to certain data, preventing unauthorized users from accessing data, and ensuring data security.

Ø System Stored Procedures

A system stored procedure is a system-created stored procedure designed to easily query information from system tables or to complete administrative tasks or other system administration tasks related to updating database tables. System stored procedures are primarily stored in the master database and are stored procedures that begin with an "SP" underscore. Although these system stored procedures are in the master database, we can call the system stored procedures in other databases. Some system stored procedures are automatically created in the current database when a new database is created.

Common system stored procedures are:

Copy Code code as follows:

exec sp_databases; --View the database
exec sp_tables; --View Table
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 stored procedure creation, definition statements
EXEC sp_rename Student, stuinfo;--modify the name of the table, index, column
exec sp_renamedb mytempdb, mydb;--change database name
exec sp_defaultdb ' master ', ' MyDB ';--Change the default database for login names
EXEC sp_helpdb;--database help, querying database information
exec sp_helpdb master;

System Stored Procedure Example:
--Table renaming

exec sp_rename ' stu ', ' stud ';
SELECT * FROM stud;

--Rename Name

exec sp_rename ' stud.name ', ' sname ', ' column ';
exec sp_help ' stud ';

--Renaming an index

EXEC sp_rename n ' student.idx_cid ', n ' Idx_cidd ', n ' index ';
EXEC sp_help ' student ';

--Querying 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 procedures

1. Create syntax

create proc | Procedure Pro_name
[{@ parameter data type} [= default] [output],
{@ parameter data type} [= default value] [Output],
....
]
As
Sql_statements

2. To create a stored procedure with no parameters

--Create a stored procedure

if (exists (SELECT * from sys.objects where name = ' proc_get_student '))
  drop proc proc_get_student
go
Create P ROC proc_get_student
as
  select * from student;

--Invoke, execute stored procedure

exec proc_get_student;

3. Modify the stored procedure

--Modify the stored procedure

ALTER proc Proc_get_student
as
select * from student;

4, with the parameter stored procedures

--With parameter stored procedures

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 * Student where ID between @startId and @endId go

exec proc_fi Nd_stu 2, 4;

5. Parameter stored procedure with wildcard characters

--with wildcard parameter stored procedure

if (object_id (' proc_findstudentbyname ', ' P ') is not null)
  drop proc proc_findstudentbyname
go
create proc  Proc_findstudentbyname (@name varchar = '%j% ', @nextName varchar = '% ') as
  select * from student where name Like @name and name like @nextName;
Go

exec proc_findstudentbyname;
exec proc_findstudentbyname '%o% ', ' t% ';

6, with output parameters stored procedures

if (object_id (' Proc_getstudentrecord ', ' P ') is not null)
  drop proc Proc_getstudentrecord
go
create proc Proc_getstudentrecord (
  @id int,--The default input parameter
  @name varchar ()--Output parameters
  @age varchar () 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,
    @temp varchar ();
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 RECOMPILE Not Cached

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

exec proc_temp;

8. Encrypt 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 parameter

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,
    @age int;
exec proc_cursor @cur = @exec_cur output;--Call stored procedure
fetch next from @exec_cur into @id, @name, @age;
while (@ @fetch_status = 0)
begin to
  fetch the 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 procedures, 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 bet Ween @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 a user-defined error message, you can specify a severity level, and set the system variable to record the errors that occurred.

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 the sysmessages system table

# msg_str: User-defined information with a maximum length of 2047 characters.

# severity: User-defined severity level associated with this message. When you use msg_id to raise a user-defined message that is created using sp_addmessage, the severity specified in sp_addmessage is overwritten by specifying the seriousness on the RAISERROR.

Any user can specify a 0-18 direct severity level. Only users who are common to the sysadmin fixed server role or have ALTER trace permissions can specify a 19-25 direct severity level. The security level between 19-25 requires the WITH LOG option.

# state: Any integer between 1 and 127 direct. The state default value 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);

The above mentioned is the entire content of this article, I hope you can enjoy.