advantage:
1. Improve the generality and portability of the application: after the stored procedure is created, it can be called many times in the program without rewriting the SQL statement of the stored procedure. And database professionals can modify stored procedures at any time
Modification, and has no impact on the program source code, which greatly improves the portability of the program.
2. The speed of SQL can be improved. Stored procedures are compiled. If an operation contains a large amount of SQL code or is executed multiple times, using stored procedures is much faster than directly using a single SQL statement.
3. Reduce the burden on the server: when the user's operation is aimed at the operation of database objects, if a single call is used, a large number of SQL statements must be transmitted on the network. If a stored procedure is used,
Then the calling command of the process can be sent directly, which reduces the burden of the network.
Syntax:
1 CREATE PROC [ EDURE ] procedure_name [ ; number ]
2 [ { @parameter data_type }
3 [ VARYING ] [ = default ] [ OUTPUT ]
4 ] [ ,...n ]
5 [ WITH
6 { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
7 [ FOR REPLICATION ]
8 AS
9 [ begin ]
10 T-SQL sentence
11 [ end ]
Parameterless stored procedure:
1 --Create a file named GetStuCou Parameterless stored procedure 2 create procedure GetStuCou 3 as 4 begin 5 select * 6 from Student s 7 left join Course c on s.C_S_Id=c.C_Id 8 end 9 10 --The execution name is GetStuCou Parameterless stored procedure 11 execute GetStuCou
Stored procedure with return value:
1 --Create a file named GetStuCou_Re Stored procedure with return value
2 create procedure GetStuCou_Re
3 as
4 begin
5 insert into Course(C_Name) values('HTML5')
6 return SCOPE_IDENTITY(); -- Returns the last generated identification value for inserting data into the current table.
7 end
8
9 --The execution name is GetStuCou Stored procedure with return value
10 execute GetStuCou_Re
Stored procedure with input parameters:
1 --Create a file named GetStuCou_In Stored procedure with input parameters 2 create procedure GetStuCou_In 3 @StuNo nvarchar(64)='001' --Set default values 4 as 5 begin 6 select * from Student where S_StuNo=@StuNo 7 end 8 9 --The execution name is GetStuCou_In Stored procedure with input parameters (without parameters, i.e. using default values) 10 execute GetStuCou_In 11 12 --The execution name is GetStuCou_In Stored procedure with input parameters (incoming parameters) 13 execute GetStuCou_In '005'
Stored procedures with input and output parameters:
1 --Create a file named GetStuCou_Out Stored procedure with input and output parameters 2 create procedure GetStuCou_Out 3 @StuNo nvarchar(64), 4 @Height nvarchar(32) output 5 as 6 begin 7 if(@StuNo is not null and @StuNo <> '') 8 begin 9 select @Height=S_Height 10 from Student 11 where S_StuNo=@StuNo 12 end 13 else 14 begin 15 set @Height='185' 16 end 17 end 18 19 --The execution name is GetStuCou_Out Stored procedure with input and output parameters 20 execute GetStuCou_Out '005',null
Stored procedures with input, output parameters and result sets:
1 --Create a file named GetStuCou_DS Stored procedure with input parameters, output parameters and result set 2 create procedure GetStuCou_DS 3 @StuNo nvarchar(64), 4 @Height nvarchar(32) output 5 as 6 begin 7 if(@StuNo is not null and @StuNo <> '') 8 begin 9 select @Height=S_Height 10 from Student 11 where S_StuNo=@StuNo 12 end 13 else 14 begin 15 set @Height='185' 16 end 17 18 select s.S_Id,s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,s.S_BirthDate,c.C_Id,c.C_Name 19 from Student s 20 left join Course c on s.C_S_Id=c.C_Id 21 where S_StuNo=@StuNo 22 end 23 24 --The execution name is GetStuCou_DS Stored procedure with input parameters, output parameters and result set 25 execute GetStuCou_DS '005',null
Stored procedures that return multiple result sets:
1 --Create a file named GetStuCou_DSS A stored procedure that returns multiple result sets 2 create procedure GetStuCou_DSS 3 @StuNo nvarchar(64), 4 @Height nvarchar(32) 5 as 6 begin 7 if(@StuNo is not null and @StuNo <> '') 8 begin 9 select * 10 from Student 11 where S_StuNo=@StuNo 12 end 13 14 if(@Height is not null and @Height <> '') 15 begin 16 select * 17 from Student 18 where S_Height=@Height 19 end 20 end 21 22 --The execution name is GetStuCou_DSS A stored procedure that returns multiple result sets 23 execute GetStuCou_DSS '005','185'
In the stored procedure, you can not only query, but also add, delete and modify various operations. In fact, storage is a code block composed of many T-SQL statements.
Create variables, assign variables, create table variables and temporary tables in stored procedures:
1 --Create a file named GetStuCou_Ext A stored procedure that returns multiple result sets 2 create procedure GetStuCou_Ext 3 @StuNo nvarchar(64), 4 @Height nvarchar(32) 5 as 6 begin 7 declare @Var nvarchar(10) --Define variables 8 9 set @Var='123' --Assignment variable 10 11 --Define table variables 12 declare @StuTab table 13 ( 14 ID int not null primary key, 15 StuNo nvarchar(50) unique, 16 Name varchar(50), 17 Sex varchar(10), 18 Height varchar(10) 19 ) 20 --Table variables can only be defined with constraints 21 22 --Define temporary tables 23 create table #Tab 24 ( 25 ID int not null primary key, 26 StuNo nvarchar(50), 27 Name varchar(50), 28 Sex varchar(10), 29 Height varchar(10) 30 ) 31 32 alter table #Tab add constraint S_UNIQUE unique(StuNo) 33 34 --Temporary tables can add constraints later 35 36 if(@StuNo is not null and @StuNo <> '') 37 begin 38 insert into @StuTab(ID,StuNo,Name,Sex,Height) --Insert data into table variables 39 select S_Id,S_StuNo,S_Name,S_Sex,S_Height 40 from Student 41 where S_StuNo=@StuNo 42 43 insert into #Tab(ID,StuNo,Name,Sex,Height) -- insert data into temporary table 44 select S_Id,S_StuNo,S_Name,S_Sex,S_Height 45 from Student 46 where S_StuNo=@StuNo 47 end 48 49 if(@Height is not null and @Height <> '') 50 begin 51 insert into @StuTab(ID,StuNo,Name,Sex,Height) --Insert data into table variables 52 select S_Id,S_StuNo,S_Name,S_Sex,S_Height 53 from Student 54 where S_Height=@Height 55 56 insert into #Tab(ID,StuNo,Name,Sex,Height) -- insert data into temporary table 57 select S_Id,S_StuNo,S_Name,S_Sex,S_Height 58 from Student 59 where S_Height=@Height 60 end 61 62 SELECT * FROM @StuTab 63 select * from #Tab 64 end 65 66 --The execution name is GetStuCou_DSS A stored procedure that returns multiple result sets 67 execute GetStuCou_Ext '005','185'
Dynamic execution of SQL statements by stored procedures:
We can see that if more than '5007' is passed in, we need to use a comma (Student ID) to separate the incoming statement. If more than '6007' is used at one time, we can use it.
1 create proc GetStus
2 @StuNo nvarchar(500)
3 as
4 begin
5 declare @Sql nvarchar(3000)
6
7 if(@StuNo is not null and @StuNo <> '')
8 begin
9 set @Sql=' select * from Student where S_StuNo in ('+@StuNo+') '
10 end
11
12 exec (@Sql) --Execution dynamics sql
13 end
14
15 exec GetStus '003,005,009' --Execute stored procedure GetStus
Now there is another problem. I want to return the variable value when executing dynamic sql. For example, I need to return the course ID when executing dynamic sql, and then query the course according to the course ID.
Using system stored procedures sp_ Execute sql assigns values to variables dynamically.
After modifying the stored procedure:
1 ALTER proc [dbo].[GetStus]
2 @StuNo nvarchar(500)
3 as
4 begin
5 declare @Sql nvarchar(3000)
6 declare @C_Id int
7 declare @Cou int
8
9 if(@StuNo is not null and @StuNo <> '')
10 begin
11 set @Sql=' select @CId=C_S_Id,@cou=count(1) from Student where S_StuNo in ('+@StuNo+') group by C_S_Id '
12 end
13
14 exec sp_executesql @Sql,N'@CId int output,@cou int output',@CId = @C_Id output,@cou = @Cou output;
15
16 select @C_Id,@Cou --View the value of the returned variable
17
18 select * from Course where C_Id=@C_Id
19 end
20
21 exec GetStus '005' --Execute stored procedure GetStus