SQL Server stored procedure in-depth analysis (very detailed)

Preface

Although the current orm and nosql operations on data will reduce the number of t-SQL programming, t-SQL should be mastered by every programmer, in the next two weeks, we will systematically summarize this knowledge point.

1. What is a stored procedure?

Stored Procedures in Transact-SQL are very similar to methods in the net language. They can be called repeatedly. After the stored procedure is executed once, you can cache the statements so that the statements in the cache are directly used for the next execution. This way

To improve the performance of stored procedures.

1. Stored Procedure is a set of SQL statements for specific functions. Compiled and stored in the database. You can specify the name of the stored Procedure and provide parameters for execution.

2. Stored procedures can contain logical control statements and data manipulation statements. They can accept parameters, output parameters, return one or more result sets, and return values.

3. Since the stored procedure is compiled on the database server and stored in the database at the time of creation, the stored procedure runs faster than a single SQL statement block. At the same time, because you only need to provide the stored procedure name and necessary parameter information during the call, it can also reduce network traffic and simple network burden to a certain extent.

2. Advantages of stored procedures

2.1 stored procedures allow standard component-based programming

After a stored procedure is created, it can be called and executed multiple times in the program without re-writing the SQL statement of the stored procedure. Database Professionals can modify the stored procedure at any time, but it has no impact on the application source code, which greatly improves the program portability.

2.2 fast execution of stored procedures

If an operation contains a large number of T-SQL statement codes that are executed multiple times, the stored procedure is much faster than the batch execution. Because the stored procedure is pre-compiled, when a stored procedure is run for the first time, the query optimizer analyzes and optimizes the stored procedure and provides the storage plan that is finally stored in the system table. The T-SQL statement of batch processing needs to be pre-compiled and optimized every time, so the speed will be slower.

2.3 stored procedures reduce network traffic

For the same database object operation, if the T-SQL statements involved in this operation are organized into a stored procedure, when the stored procedure is called on the client, only this call statement is passed in the network; otherwise, multiple SQL statements are passed. This reduces network traffic and network load.

2.4 stored procedures can be fully utilized as a security mechanism

The system administrator can restrict the permissions of a stored procedure to prevent unauthorized users from accessing data and ensure data security.

3. Disadvantages of stored procedures

3.1. Running speed

Therefore, for simple SQL statements, stored procedures have no advantages.

3.2. Development and debugging

Development and debugging of stored procedures are more difficult than general programs (the old version of DB2 can only write stored procedures in C, but also a disaster ).

3.3. Portability:

This is the main reason why I do not select a stored procedure. Generally, applications are bound to a database. Otherwise, you cannot optimize database access to improve performance.

4. Stored Procedure

A system stored procedure is a stored procedure created by the system. It is designed to conveniently query information from the system table or complete management tasks related to updating database tables or other system management tasks. The system stored procedures are stored in the master database, which begins with an underscore (sp. Although these system stored procedures are in the master database, we can still 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.

View Code
. System stored procedure example

-- Rename a table
Exec sp_rename 'Stu', 'stud'; -- rename a column
Exec sp_rename 'Stud. Name', 'sname', 'column ';
Exec sp_help 'stud ';
-- Rename an index
Exec sp_rename N 'student. idx_cid ', n'idx _ Cid', 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 % ';

. Custom stored procedures

4.2.1. Create syntax

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

4.2.2. Create a stored procedure without parameters

-- Create a stored procedure

If (object_id ('proc _ get_student ', 'P') is not null) -- determines whether the stored procedure has another 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;
4.2.3. Modify the stored procedure

-- Modify the stored procedure
Alter proc proc_get_student
As
Select * from student;
4.2.4. Stored procedure with parameters

-- Stored procedure with parameters

If (object_id ('proc _ find_stu ', 'P') is not null) -- determines whether a stored procedure exists.
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;

4.2.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 % ';

4.2.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;

4.2.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;

4.2.8. Encrypted stored procedures

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

4.2.9. Stored procedure with cursor parameters

View Code
4.2.9. 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;

5. 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 );
6. There are two methods to return parameters for the stored procedure in SqlServer.

The stored procedure in SQL Server can return parameters in two ways.

1. Return
2. OutPut
The first type can only return INT type data, while the second type can be any (that is, the Object type)
The method used today is the second method to obtain the returned value. First, let's look at a simple stored procedure.

CREATE Procedure [dbo]. [pro_ty_getFlowByFbillID]
@ Fid int output -- defines the output variable and the OUTPUT variable.
As
SET NOCOUNT ON

Select @ FID = Fite -- assign values
From [dbo]. [t_bm_exp]
Where FbillID = 1
SET NOCOUNT OFF
The output variable has been defined in the stored procedure. The net call value can be set below.
SqlParameter sp =
{-- Define parameters
New SqlParameter ("@ fid", SqlDbType. INT, 4)
};
-- Obtain parameters
Sp [0]. Direction = ParameterDirection. Output;
/* Net calls the stored procedure */
-- Omitted
Return Convert. ToInt32 (sp [0]. value. ToString ());

Complete.

7. Stored procedures written in previous work [please try it a long time ago]

7.1 workflow to-do list page

-- ===================================================== ======
-- Author: wangyanling
-- Create date: 2014-06-03
-- Description: Workflow to-do list page
-- ===================================================== ======
Alter procedure [dbo]. [PageWorkFlowToDo]
@ Pageindex int,
@ Pagesize int,
@ Uid varchar (200 ),
@ StrWhere varchar (500)
AS
BEGIN
Declare @ SQL varchar (4000)
Set @ SQL = 'SELECT top '+ CAST (@ pagesize as varchar) + 'db_userwork.uwid, db_userwork.uid, db_userwork.Title, db_userwork.IniUid, u_user.UName as inininame,
Convert (varchar (100), db_userwork.IniStartTime, 20) as IniStartTime, convert (varchar (100 ),
Db_userwork.endTime, 20) as endTime, db_userwork.State,
S_FInfo.ID as FormID, db_userwork.ImpLevel, db_userwork.DueTime, db_userwork.wsuid,
Db_userwork.policytype, db_userwork.IfTrans, db_userwork.ID,
Case S_FInfo.FrmType when '1' then ''WorkFlowForm. aspx''
When ''2' then''' DeWorkFlowForm. aspx''
When ''3''then'' AddTeamWork. aspx''
Else S_FInfo.FrmHtml end as FrmHtml, db_Work_Case.ExeStep, db_userwork.IfUpdate, db_userwork.FID,
Db_userwork.NodeID, ExtParam from db_userwork
Left join S_FInfo on db_userwork.wsuid = S_FInfo.WID
Left join db_Work_Case on db_userwork.ID = db_Work_Case.ID
Left join u_user on db_userwork.IniUid = u_user.UId
Where db_userwork.uid = '+ @ uid +' and db_userwork.State = 1 and db_userwork.uwid not in (
Select top '+ CAST (@ pageindex-1) * @ pagesize) as varchar) + 'DB _ userwork. uwid from db_userwork
Left join S_FInfo on db_userwork.wsuid = S_FInfo.WID
Left join db_Work_Case on db_userwork.ID = db_Work_Case.ID
Left join u_user on db_userwork.IniUid = u_user.UId
Where db_userwork.uid = '+ @ uid +' and db_userwork.State = 1 and 1 = 1' + @ strWhere + 'Order by IniStartTime desc
) And 1 = 1'
If (@ strWhere <> '')
Begin
Set @ SQL = @ SQL + @ strWhere + 'Order by IniStartTime desc ';
End
Else
Begin
Set @ SQL = @ SQL + 'Order by IniStartTime desc ';
End
Exec (@ SQL)
END

 

. Obtain the corresponding group number by department name

</Pre> cursor: <pre code_snippet_id = "570631" snippet_file_name = "blog_20150103_3_3319497" name = "code" class = "SQL"> -- ======== ==================================
-- Author: wangyanling
-- Create date: 2013-05-23
-- Description: obtains the group number by department name.
-- ===================================================== ======
Alter procedure [dbo]. [GetGroupIDByOnLine]
-- Add the parameters for the stored procedure here
@ GName varchar (200)
AS
BEGIN
-- Set nocount on added to prevent extra result sets from
-- Interfering with SELECT statements.
Set nocount on;
Create table # temp
    (
GroupID int
    )
Create table # tempTwoTable
    (
GroupID int
    )
Insert into # temp select GId from g_group where PId =
    (
Select GId from g_group where GName = @ GName
    )
Declare @ count int
Select @ count = COUNT (*) from # temp
If (@ count> 0)
Begin
Declare @ GroupID int
Declare cur cursor for select GroupID from # temp
Open cur
Fetch next from cur into @ GroupID
While @ FETCH_STATUS = 0 -- determines whether data is successfully obtained
Begin
Insert into # tempTwoTable select GId from g_group where PId = @ GroupID
Insert into # tempTwoTable select @ GroupID
Fetch next from cur into @ GroupID
End
Close cur -- close the cursor
Deallocate cur
Insert into # tempTwoTable select GId from g_group where GName = @ GName
End
Else
Begin
Insert into # tempTwoTable select GId from g_group where GName = @ GName
End
----
Declare @ GroupIDs varchar (100)
Declare @ DouHao nvarchar (max)
Set @ DouHao =''
Declare curDouHao cursor
Select GroupID from # tempTwoTable
Open curDouHao
Fetch next from curDouHao into @ GroupIDs
While @ FETCH_STATUS = 0 -- determines whether data is successfully obtained
Begin
Set @ DouHao = @ DouHao + Convert (varchar (20), @ GroupIDs) + ','
Fetch next from curDouHao into @ GroupIDs
End
Close curDouHao -- close the cursor
Deallocate curDouHao
Select SUBSTRING (@ DouHao, 1, LEN (@ DouHao)-1)
Drop table # tempTwoTable
Drop table # temp
END

. Stored procedure job operations

Alter procedure [dbo]. [add_job] @ portal_dbName varchar (200)
AS
BEGIN
DECLARE @ jobid uniqueidentifier, @ jobname sysname, @ sysdatetime varchar (500)
SET select @ sysdatetime = convert (varchar (23), Datename (yyyy, getdate () + convert (varchar (23), Datename (mm, getdate ())) + convert (varchar (23), Datename (dd, getdate () + Datename (hh, getdate () + Datename (n, getdate () + Datename (ss, getdate ())
SET @ jobname = n' portal _ safe_job '+ @ sysdatetime

If exists (SELECT * FROM msdb. dbo. sysjobs WHERE name = @ jobname)
EXEC msdb. dbo. sp_delete_job @ job_name = @ jobname

EXEC msdb. dbo. sp_add_job
@ Job_name = @ jobname,
@ Job_id = @ jobid OUTPUT
-- Define job steps
DECLARE @ SQL nvarchar (4000), @ dbname sysname
Select @ dbname = @ portal_dbName, -- The job step is important in the current database (replace the table name according to different tables)
@ SQL = n' delete from db_userOnline where hartTime <dateadd (minute,-2, getdate () or logined = 1' -- generally, jobs processed using TSQL are defined, here, the Transact-SQL statement to be executed is defined.
EXEC msdb. dbo. sp_add_jobstep
@ Job_id = @ jobid,
@ Step_name = n'step1 ',
@ Subsystem = 'tsql', -- step type, generally TSQL
@ Database_name = @ dbname,
@ Command = @ SQL

-- Create scheduling (use the following job scheduling templates)
EXEC msdb. dbo. sp_add_jobschedule @ job_id = @ jobid, @ name = n' scheduling name ',
@ Freq_type = 4, -- Daily
@ Freq_interval = 1, -- specifies the number of days that occur. Here is 1 day.
@ Freq_subday_type = 0x4, -- repeat mode, 0x1 = at the specified time, 0x4 = how many minutes, 0x8 = how many hours to execute
@ Freq_subday_interval = 1, -- Number of repetition cycles, which is executed once every hour
@ Active_start_date = NULL, -- start date of job execution. If it is NULL, it indicates the current date. The format is YYYYMMDD.
@ Active_end_date = 99991231,
@ Active_start_time = 00000, @ active_end_time = 235959

-- Add the target server
EXECUTE msdb. dbo. sp_add_jobserver @ job_id = @ JobID, @ server_name = N' (local )'
END