SQL Server Create stored procedure--Dynamic SQL

A stored procedure (stored procedure) is a set of SQL statements designed to accomplish a particular function, compiled and stored in a server-side database, which can be used to accelerate the execution of SQL statements.

Custom stored procedures, stored procedures created by a user and capable of completing a particular function, stored procedures can have both parameters and return values, but unlike functions, the return value of a stored procedure simply indicates whether the execution was successful,

Stored procedures cannot be called directly like functions, only execute can be used to perform stored procedures.

Advantages:

1. Improve application versatility and portability: After a stored procedure is created, it can be called multiple times in a program without having to rewrite the SQL statement for the stored procedure. and database professionals can do the stored procedures at any time

Modification, and has no effect on the program source code, which greatly improves the portability of the program.

2, can improve the speed of SQL, stored procedures are compiled, if an operation contains a large number of SQL code or executed several times, then using a stored procedure is faster than the direct use of a single SQL statement execution.

3, reduce the burden of the server: When the user's operation is against the database object operation, if the use of a single call, then the network must also transfer a large number of SQL statements, if the use of stored procedures,

The call command of the direct sending process can reduce the burden on the network.

Grammar:

1 CREATE PROC [edure] procedure_name [; number] 2     [{@parameter data_type} 3         [VARYING] [= default] [out PUT] 4     ] [,... n] 5 [with 6     {RECOMPILE | Encryption | RECOMPILE, encryption}] 7 [for REPLICATION] 8 as  9 [begin]10     T-SQL statement [end]

No parameter stored procedure:

1--Create a parameterless stored procedure named Getstucou 2 CREATE PROCEDURE Getstucou 3 as 4 begin 5     SELECT * 6 from     Student s 7     lef T join Course C on s.c_s_id=c.c_id 8 End 9 10-execute a parameterless stored procedure named Getstucou execute Getstucou

Stored procedure with return value:

1--Create a stored procedure named Getstucou_re with return value 2 CREATE PROCEDURE Getstucou_re 3 as 4 begin 5 INSERT INTO     Course (c_name) VALUES (' HTM L5 ') 6     return scope_identity ();        --Returns the identity value that was last generated for inserting data into the current table. 7 End 8  9--executes a stored procedure named Getstucou with a return value of ten execute Getstucou_re

Stored procedures with input parameters:

1--Create a stored procedure named getstucou_in with input parameters 2 CREATE PROCEDURE getstucou_in 3 @StuNo    nvarchar (+) = ' 001 '        --Set default value 4 as 5 begin  6     SELECT * from Student where [email protected] 7 End 8  9--Execute stored procedure named getstucou_in with input parameters (no arguments, default values) Getstucou_in11 12--Execute a stored procedure named getstucou_in with input parameters (incoming parameters) Execute getstucou_in ' 005 '

Stored procedures with input and output parameters:

1--Create a stored procedure named Getstucou_out with input parameters and output parameters 2 CREATE PROCEDURE getstucou_out 3 @StuNo    nvarchar (4 @Height nvarchar (32) Output 5 as 6 begin 7     if (@StuNo is not null and @StuNo <> ") 8     begin 9         Select @Height =s_height         From Student one         where [email protected]12     end13     else14     begin15         Set @Height = ' 185 '     end17 End18 19--Executes a stored procedure named Getstucou_out with input parameters and output parameters, execute getstucou_out ' 005 ', NULL

Stored procedures with input, output parameters, and result sets:

1--Create a stored procedure named Getstucou_ds with input parameters, output parameters, and result sets 2 CREATE PROCEDURE Getstucou_ds 3 @StuNo    nvarchar (3), 4 @Height nvarchar 2) output 5 as 6 begin 7     if (@StuNo is not null and @StuNo <> ") 8     begin 9         Select @Height =s_height 10
   from Student one         where [email protected]12     end13     else14     begin15         Set @Height = ' 185 '     end17     Select S.s_id,s.s_stuno,s.s_name,s.s_sex,s.s_height,s.s_birthdate,c.c_id,c.c_name19 from     Student S20 Left     join Course C on s.c_s_id=c.c_id21     where [email protected]22 end23 24--Execute with input parameters, output parameters, and result set named Getstucou_ds Stored procedure execute getstucou_ds ' 005 ', NULL

To return a stored procedure for multiple result sets:

1--Create a stored procedure named GETSTUCOU_DSS that returns multiple result sets 2 CREATE PROCEDURE Getstucou_dss 3 @StuNo    nvarchar (), 4 @Height nvarchar (32) 5 As 6 begin 7     if (@StuNo is not null and @StuNo <> ") 8     begin 9         Select *10 from         Student one         where [Email protected]12     end13     if (@Height is not null and @Height <> ")     begin16         SELECT * +         F Rom Student18         where [email protected]19     end20 end21 22-Execute stored procedure named GETSTUCOU_DSS that returns multiple result sets, execute getstucou_ DSS ' 005 ', ' 185 '

Stored procedures can not only query, but also to make a variety of additions and deletions to the operation. Storage is actually a block of code that consists of many T-SQL statements.

Create variables, assignment variables, create table variables, and temporary tables in stored procedures:

 1--Create a stored procedure named Getstucou_ext that returns multiple result sets 2 CREATE PROCEDURE Getstucou_ext 3 @StuNo nvarchar (), 4 @Height nvarchar (32) 5 As 6 begin 7 declare @Var nvarchar (10)--Define variable 8 9 Set @Var = ' 123 '--assignment Variable 10 11--Define table variable [DECLARE] @StuTab table (ID int NOT null primary key,15 Stuno nvarchar () unique,16 Name V     Archar (10), 22 Sex varchar (20), Height varchar (19)----table variable can only be added at the time of the definition of constraint 21----Define temporary table 23 CREATE TABLE #Tab24 (ID int NOT null primary key,26 Stuno nvarchar (), Name Varc  Har (+), (+) (+), Height varchar (TEN), ALTER TABLE #Tab add constraint S_unique Unique (Stuno) 33 34--temporary table can be added after the constraint (@StuNo is not null and @StuNo <> ") PNs begin38 in Sert into @StuTab (id,stuno,name,sex,height)--inserting data into table variable----Select S_ID,S_STUNO,S_NAME,S_SEX,S_HEIGHT40 fr Om Student-WHere [email protected]42] insert INTO #Tab (id,stuno,name,sex,height)-inserts data into temporary table, select s_id,s_s  Tuno,s_name,s_sex,s_height45 from Student, where [email protected]47 end48 (@Height is         Not null and @Height <> ") begin51 insert INTO @StuTab (id,stuno,name,sex,height)--insert data into table variable 52         Select S_id,s_stuno,s_name,s_sex,s_height53 from Student54 where [email protected]55 56 Insert into #Tab (id,stuno,name,sex,height)-insert data into temporary table s_id,s_stuno,s_name,s_sex,s_height58 Select fro  M Student59 WHERE [email protected]60 end61 select * FROM @StuTab63 select * from #Tab64 end65 66--Execute a stored procedure named GETSTUCOU_DSS that returns multiple result sets Getstucou_ext ' 005 ', ' 185 '

The stored procedure executes the SQL statement dynamically:

The above can be seen that our incoming parameters (number) is a single, then if you pass in a number of numbers (separated by commas, that is, ' 005,006,007 '), which requires the use of dynamic splicing and execute SQL statements.

1 Create proc getstus 2 @StuNo nvarchar 3 as 4 begin 5     declare @Sql nvarchar (+) 6  7     if (@StuNo is not n Ull and @StuNo <> ") 8     begin 9         Set @Sql = ' SELECT * from Student where S_stuno in (' [email protected]+ ') ' 10     end11     exec (@Sql)    -Execute dynamic Sql end14 getstus ' 003,005,009 '        -Execute stored procedure Getstus

Now there is a problem, I want to execute dynamic SQL and return the variable value, for example I now need to execute dynamic SQL when the course ID is returned, and then query the course based on the course ID.

Use system stored procedures sp_executesql Dynamic SQL to assign values to variables.

After you modify the stored procedure:

1 ALTER proc [dbo].  [Getstus] 2 @StuNo nvarchar ($) 3 as 4 begin 5     declare @Sql nvarchar (+) 6     declare @C_Id int 7     DECLARE @Cou int 8  9     if (@StuNo is not null and @StuNo <> ")     begin11         Set @Sql = ' Select @CId =c_s_id, @cou =count ( 1) from Student where S_stuno in (' [email protected]+ ') GROUP by c_s_id '     end13     exec sp_executesql @Sql, N ' @CI d int output, @cou int output ', @CId = @C_Id output, @cou = @Cou output;15     Select @C_Id, @Cou    --View the value of the return variable 18
   
    select * from Course where [email protected]_id end20 exec getstus ' 005 '        -Execute stored procedure Getstus
   

Ps:sp_executesql requires dynamic SQL and dynamic SQL parameter lists to be NVARCHAR types.

The argument list for dynamic SQL must be consistent with the order of the parameter list for the externally supplied values, for example: N ' @CId int output, @cou int output ', @CId = @C_Id output, @cou = @Cou output, @CId corresponding @C_Id, @c The OU corresponds to the @Cou.

The parameter list of the dynamic SQL can have the same name as the parameter list parameter name of the externally supplied parameter, if not the same, the indication should be displayed, for example: N ' @CId int output, @cou int output ', @CId = @C_Id output, @cou = @Cou output; That is @CId = @C_Id and @cou = @Cou.

SQL Server Create stored procedure--Dynamic SQL