May 16, 2021 SQL
1. Fully parse SQL stored procedures
2. The benefits of stored procedures
3. The basic syntax of 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.
--------------创建存储过程-----------------
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 --在存储过程中能调用另外一个存储过程,而不能删除另外一个存储过程
procedure_name
The name of the stored procedure, Plus the name of the storedure, The local temporary stiled procedure, and the # is a global temporary stored procedure.
number
is an optional integer that groups procedures of the same name so that you can remove them with a DROP PROCEDURE statement. F
or example, an application named orders uses a procedure that can be named orderproc; 1
、orderproc; 2
and so on. T
he DROP PROCEDURE orderproc statement removes the entire group. I
f the name contains a bounding identifier, the number should not be included in the identifier, and only the appropriate bounding procedure_name should be used before and after the procedure_name.
@parameter
parameters of the stored procedure. Y
ou can have one or more. T
he user must provide the value of each declared parameter at the time of execution (unless the default value for that parameter is defined).
Stored procedures can have up to 2100 parameters.
data_type
data type of the argument. A
ll data types, including text, ntext, and image, can be used as parameters for stored procedures. H
owever, cursor data types can only be used for OUTPUT parameters. I
f the specified data type is cursor, you must also specify both the VARYING and OUTPUT keywords.
For more information about the types of data SQL Server provides and their syntax, see Data Types.
VARYING
Specifies the set of results supported as output parameters (dynamically constructed by stored procedures and content can vary). O
nly applies to cursor parameters.
default
The default value of the argument. I
f the default value is defined, you do not have to specify the value of the parameter to execute the procedure. T
he default value must be constant or NULL. I
f the procedure will use the LIKE keyword for this parameter, the default values can contain wildcards (, and ,
OUTPUT
Indicates that the argument is a return parameter. T
he value of this option can be returned to EXEC (UTE). U
se the OUTPUT parameter to return information to the calling procedure. T
ext, ntext, and image parameters can be used as OUTPUT parameters. T
he output parameters that use the OUTPUT keyword can be cursor placeholders.
RECOMPILE
that SQL Server will not cache the schedule for the procedure, which will be recompiled at runtime. U
se the RECOMPILE option when you are using atypical or temporary values and do not want to overwrite execution plans that are cached in memory.
ENCRYPTION
An entry that indicates that the SQL Server encrypted syscomments table contains the text of the CREATE PROCEDURE statement. U
sing ENCRYPTION prevents procedures from being published as part of SQL Server replication. E
xplain that during the upgrade process, SQL Server recreated the encryption process using encrypted comments stored in syscomments.
FOR REPLICATION
Specifies that stored procedures created for replication cannot be performed at the Subscriber. S
tored procedures created with the FOR REPLICATION option can be used as stored procedure filtering and can only be performed during replication. T
his option cannot be used with the WITH RECOMPILE option.
sql_statement
Any number and type of Transact-SQL statements to be included in the procedure. B
ut there are some limitations.
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.
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
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
These are global variables, and here's how to understand local variables
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
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
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION }
]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
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
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:
For example, PROC_SEINFO the SQL statement for the stored procedure is as follows.
DROP PROCEDURE PROC_SEINFO
For example, delete multiple stored procedures proc10, proc20, and proc30.
DROP PROCEDURE proc10, proc20, proc30
For example, delete the stored procedure group procs, which contain stored procedures proc1, proc2, proc3.
DROP PROCEDURE procs
Note: SQL statement DROP cannot delete a single stored procedure in a stored procedure group.
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();
}}