Detailed instances fully parse SQL stored procedures

Fully parse SQL stored procedures

Stored Procedure, a set of SQL statements that perform specific functions, is similar to a programming language and includes data types, process controls, inputs and outputs, and its own library of functions. A stored procedure can be said to be a set of records, consisting of blocks of code made up of T-SQL statements that implement functions like a method (additions and deletions to a single or multiple table) and then give the block a name and call it when it is used. However, SQL stored procedures are still abstract and difficult for some beginners to understand, so this article will provide a shallow-to-deep analysis of SQL stored procedures to help you learn about them.

The benefits of stored procedures

1. Stored procedures are compiled only at the time of creation, and do not need to be recompiled every time the stored procedure is executed, where as a general SQL statement compiles every time they are executed, so using stored procedures can improve the speed of database execution and be more efficient than T-SQL statements.
2. When complex operations on a database are performed, they can be encapsulated in stored procedures and combined with transactions provided by the database.
3. A stored procedure can replace a large heap of T-SQL statements when a program interacts in a network, so it can also reduce the traffic volume of the network and improve the communication rate.
4. Stored procedures can be reused, reducing the workload of database developers.
5. High security allows you to set that only certain users have access to the specified stored procedure
   

The basic syntax of stored procedures

--------------创建存储过程-----------------

CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

--------------调用存储过程-----------------

EXECUTE Procedure_name '' --存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value

--------------删除存储过程-----------------

drop procedure procedure_name    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

Let's take a look at stored procedures in detail through table Student, because the example is simple to understand the simple use of stored procedures.

No parameter stored procedures

Select all the information in the Sent table

create proc StuProc
as      //此处 as 不可以省略不写
begin   //begin 和 end 是一对，不可以只写其中一个，但可以都不写
select S#,Sname,Sage,Ssex from student
end
go

There are parameter stored procedures

Global variable

A global variable, also known as an external variable, is defined outside the function and has a scope that starts at the variable definition and ends at the end of the program file.

Select the student information for the specified name:

create proc StuProc
@sname varchar(100)   
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go

exec StuProc '赵雷'   //执行语句

Above, you can assign values to variables externally, or you can set default values for variables directly inside

create proc StuProc
@sname varchar(100)='赵雷'
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go

exec StuProc

You can also output the contents of the variable, using output

create proc StuProc
@sname varchar(100),
@IsRight int  output //传出参数
as 
if exists (select S#,Sname,Sage,Ssex from student where sname=@sname)
set @IsRight =1
else
set @IsRight=0
go

declare @IsRight int 
exec StuProc '赵雷' , @IsRight output
select @IsRight

The local variable

Local variables are also known as internal variables. L ocal variables are defined within a function. Its scope is limited to the inside of the function, and it is illegal to use this variable after leaving the function.

The definition of a local variable

It must be determined with the Declare command before it can be used, declare . . . variable name data type . . .

The assignment method for the local variable

set,"variable name,"expression", or "variable name"-expression....................

The display of local variables

create proc StuProc
as 
declare @sname varchar(100)
set @sname='赵雷'
select S#,Sname,Sage,Ssex from student where sname=@sname
go

exec StuProc

What if you want to show the data of local variables?

create proc StuProc
as 
declare @sname varchar(100)
set @sname=(select Sname from student where S#=01)
select @sname
go

exec StuProc

More detailed example action learning

IF EXISTS (SELECT name  
   FROM   sysobjects  
   WHERE  name = 'Proc_InsertEmployee'  
   AND          type = 'P') 
DROP PROCEDURE Proc_InsertEmployee 
GO 
CREATE PROCEDURE Proc_InsertEmployee 
@PName nvarchar(50), 
@PSex nvarchar(4), 
@PAge int, 
@PWage money 
AS 
begin 
   declare @PID nvarchar(50) 
   select @PID=Max(员工编号) from tb_Employee 
   if(@PID is null) 
       set @PID='P1001' 
   else 
       set @PID='P'+cast(cast(substring(@PID,2,4) as int)+1 as nvarchar(50)) 
   begin 
       insert into tb_Employee values(@PID,@PName,@PSex,@PAge,@PWage) 
   end 
end 
go 

Modifications to the stored procedure

ALTER PROC [ EDURE ] procedure_name [ ; number ] 
    [ { @parameter data_type }  
         [ VARYING ] [ = default ] [ OUTPUT ] 
    ] [ ,...n ]  
[ WITH 
    { RECOMPILE | ENCRYPTION 
        | RECOMPILE , ENCRYPTION   }  
] 
[ FOR REPLICATION ]  
AS 
    sql_statement [ ...n ]

Description of the parameters

procedure_name: The name of the stored procedure that you want to change.

Crosslink: Other parameters for the ALTER PROCEDURE statement are the same as for the CREATE PROCEDURE statement, see "Create stored procedure parameters" above.

For example, modify the stored procedure PROC_SEINFO to query employee information older than 35. The SQL statement is as follows:

ALTER PROCEDURE [dbo].[PROC_SEINFO] 
AS 
BEGIN 
SELECT * FROM tb_Employee where 员工年龄>35 
END

The deletion of the stored procedure

Using Microsoft SQL Server Mangement to remove the stored procedure, the steps are as follows:

(1) In Object Explorer at SQL Server Management Studio, select the database in which you want to delete the stored procedure (e.g., db_student), and then select Programmability under that database.

(2) Open the Stored Procedures folder, right-click the stored procedures you want to delete (e.g., PROC_SEINFO) and select the Delete command in the pop-up shortcut menu.

(3) Click the OK button to delete the selected stored procedure.

Note: When you delete a data table, the associated stored procedure is not deleted, but its stored procedure cannot be executed.

Use T-SQL to remove stored procedures:

The DROP PROCEDURE statement is used to remove one or more stored procedures or procedure groups from the current database.

Grammar:

DROP PROCEDURE { procedure } [ ,...n ]

Description of the parameters:

• Procedure: The name of the stored procedure or stored procedure group that you want to delete. T he procedure name must comply with the identifier rules. Y ou can choose whether to specify the procedure owner name, but you cannot specify the server name and database name.
  
• n: is a placeholder that indicates that multiple procedures can be specified.
  

For example, PROC_SEINFO the SQL statement for the stored procedure is as follows.

DROP PROCEDURE PROC_SEINFO

DROP PROCEDURE proc10, proc20, proc30

DROP PROCEDURE procs

Note: SQL statement DROP cannot delete a single stored procedure in a stored procedure group.

Apply stored procedures to verify the user's login identity:

Currently, there are several ways to verify a user's login identity, and user authentication by calling stored procedures is one of the best solutions available. B ecause stored procedures are compiled on the server at the time they are created, they are executed much faster than a single SQL statement.

This example is to verify that the user name and password of the user who logged on are correct by calling the stored procedure. R un this instance, enter the appropriate username and password in the User Name and Password text boxes, and click the Sign in button.

Program development steps:

(1) Create a new website and name it "index" and the default home page name is Default .aspx.

(2) The default .aspx the controls involved in the page are shown in Table 1.

(3) The main program code is as follows.
Open SQL Server Management Studio and connect to the database in SQL Server2005. C lick the button in the toolbar to create a new query editor. I n the query editor, create a stored procedure to verify the identity of the logged-in PROC_EXISTS, the specific SQL statement is as follows:

CREATE PROC PROC_EXISTS 
( 
@UserName NVARCHAR(20), 
@PassWord NVARCHAR(20), 
@ReturnValue int OUTPUT 
) 
AS 
IF EXISTS(select * from tb_member where userName=@UserName AND passWord=@PassWord) 
       set @ReturnValue= 100 
ELSE 
       set @ReturnValue= -100 
GO

Under the Click event of the Login button, a stored procedure is performed to verify the identity of the logged-in user, and if the user name and password entered are correct, a pop-up dialog box prompts the user to log on successfully, as follows:

protected void btnLogin_Click(object sender, EventArgs e) 
    { 
        //连接数据库 
        myConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); 
        myCmd = new SqlCommand("PROC_EXISTS", myConn);   //调用存储过程，判断用户是否存在
        myCmd.CommandType = CommandType.StoredProcedure; 
        //为存储过程的参数赋值 
        SqlParameter userName=new SqlParameter("@UserName", SqlDbType.NVarChar, 20); 
        userName.Value=this.txtName.Text.Trim(); 
        myCmd.Parameters.Add(userName); 
        SqlParameter passWord=new SqlParameter("@PassWord", SqlDbType.NVarChar, 20); 
        passWord.Value = this.txtPassword.Text.Trim(); 
        myCmd.Parameters.Add(passWord); 
        //指出该参数是存储过程的OUTPUT参数 
        SqlParameter ReturnValue = new SqlParameter("@ReturnValue",SqlDbType.Int ,4); 
        ReturnValue.Direction = ParameterDirection.Output; 
        myCmd.Parameters.Add(ReturnValue); 
        try 
        { 
            myConn.Open(); 
            myCmd.ExecuteNonQuery(); 
            if (int.Parse(ReturnValue.Value.ToString()) == 100) 
            { 
                Response.Write("<script>alert('您是合法用户，登录成功！')</script>"); 
                return; 
            } 
            else 
            { 
                Response.Write("<script>alert('您输入的用户名和密码不正确，请重新输入！')</script>"); 
                return; 
            } 
        } 
        catch(Exception ex) 
        { 
            Response.Write(ex.Message.ToString()); 
        } 
        finally 
        { 
            myConn.Close(); 
            myConn.Dispose(); 
            myCmd.Dispose(); 
        }}