Use of SQL Server stored procedures

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.

What is a stored procedure:

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.

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:

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

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

Go

EXEC Proc_find_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 (20) = '% ')

As

SELECT * FROM student where name like @name and name is 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,--Default input parameters

@name varchar out,--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 (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 RECOMPILE 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. 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 (20),

@age int;

EXEC proc_cursor @cur = call stored procedure @exec_cur output;--

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 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 between @startIndex and @endIndex

Go

--drop proc Pro_page

EXEC pro_page 1, 4

--

--Paging stored procedures

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 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);