SQL Server Stored Procedures

The stored procedure (stored procedure) is sometimes called sproc, it is a real script, more accurately, it is batch processing (batch), but neither is very exact, it is stored with the database rather than in a separate file.

The stored procedure has input parameters, output parameters, and return values.

first, create a stored procedure

　　The method of creating a stored procedure is the same as creating any other object in the database except that he uses the AS keyword. The basic syntax for a stored procedure is as follows:

CREATE PROCEDURE|PROC <SPROC Name> 　　 [<parameter name> [schema.] <Data type> [VARYING] [=<default value>] [Out[put]][READONLY] 　　 [, <parameter name> [schema.] <Data type> [VARYING] [=<default value>] [Out[put]][READONLY] 　　 [,... ...]][With RECOMPILE | Encryption | [EXECUTE as {CALLER | Self | OWNER | < ' user name ';}] 　　  as 　　 <Code> |EXTERNAL NAME<Assembly name>.<Assembly class>.<Method>

In syntax, Proc is the abbreviation for procedure, which means two options.

After the stored procedure has been named, the parameter list is followed. The parameters are optional.

The keyword as is followed by the actual code.

Examples of simple stored procedures:

CREATE PROC Spperson  as SELECT *  from Person　　

To execute a stored procedure:

EXEC Spperson

View results:

　　

second, alter modify the stored procedure

　　The difference between ALTER proc and create Proc is as follows:

• ALTER Proc expects to find an existing stored procedure, while create is not.
• ALTER proc retains any permissions that have been established on the stored procedure. It retains the object ID in the system object and allows the retention of dependencies. For example, if procedure A calls procedure B, if you delete and rebuild B, you cannot see the dependencies between the two. If you use Alter, the dependency still exists.
• ALTER Proc retains any dependency information on other objects that might invoke the modified stored procedure.

Example:

ALTER PROC Spperson  as SELECT *  from WHERE =  $　　

Third, delete stored procedures

The syntax for deleting stored procedures is the simplest:

DROP PROC | PROCEDURE <sproc name>[; ]

This completes the deletion of the stored procedure.

Iv. parameterization

If the stored procedure has no way to accept some data and tell it what to do, in most cases, the stored procedure will not be much help. For example, to delete a piece of data without specifying an ID, the stored procedure does not know which one to delete, so it is necessary to use the input parameters.

1. Declaration Parameters

The declaration parameters require the following 2 to 4 parts of the information:

• Name
• Data type
• Default value
• Direction

Its syntax is as follows:

@parameter_name [as] [=default| NULL][VARYING][OUTPUT | out]

For names, there is a simple set of rules. First, it must begin with the @ sign (as with a variable). In addition, the rule is the same as the normal variable rule except that it cannot be embedded in an empty space.

Data types and names must be declared as variables, using SQL Server built-in or user-defined data types.

When declaring a type, it is important to note that the varying and output options must also be used when declaring the cursor type parameter. At the same time, output can be abbreviated as out.

In terms of default values, parameters differ from variables. For the same situation, the variable is generally initialized to a null value, and the parameter is not. In fact, if the default is not provided, the argument is assumed to be necessary and an initial value needs to be provided when the stored procedure is called.

An example of a stored procedure that requires an incoming parameter:

CREATE PROC spname @Name nvarchar (+)  as SELECT  from  Person WHERE  like @Name + ' % ';　　

To execute a stored procedure:

EXEC ' Wine ';

The results appear as follows:

　　

　　2. Provide default values

For the parameter to be optional, you must provide a default value. The method is to add the "=" symbol and the value as the default value before the comma after the data type. Thus, the user of the stored procedure grams has decided not to provide values for this parameter or to provide their own values.

Create a stored procedure as follows:

CREATE PROCspname@Name nvarchar( -)= NULL 　　  as 　　 IF @Name  is  not NULL     　　 SELECT *  fromPersonWHERENAME= @Name 　　 ELSE     　　 SELECT *  fromPersonWHEREId=  $

Execute the following statement:

EXEC spname EXEC ' Ruyi knife Wolf '

The output results are as follows:

　　

3. Output Parameters

Here's a look at a stored procedure that gets an output parameter:

CREATE PROCInsertPerson@Id intoutput --must be indicated as output 　　 as 　　 INSERT  into PersonVALUES('Liu Bei', A, the,'Unknown','Unmarried','Kindergarten','Unknown',4999999)SET @Id = @ @IDENTITY

To execute a stored procedure:

DECLARE @Id int --in fact, the name can be different when called, for example, @num,@i, and so on.  EXEC@Id output    --note that an output SELECT is also needed here@Id　　　　

The results appear as follows:

　　

For the stored procedure itself and for its use by the calling script, the following points need to be noted:

• For output parameters in a stored procedure declaration, you need to use the Export keyword.
• As with declaring stored procedures, you must use the OUTPUT keyword when you call a stored procedure. This gives SQL Server advance notice of the special handling required for the parameter. Note, however, that if you forget to include the OUTPUT keyword, no run-time errors are generated, but the output value is not passed into the variable (the variable is most likely null).
• Variables assigned to the output result do not need to have the same name as the internal parameters in the stored procedure.
• The EXEC (or execute) keyword is required because the call to the stored procedure is not the first thing to do for the batch (if the call to the stored procedure is the first thing in the batch, you can not use exec).

Five, return value

The return value is useful for a wide range of purposes, such as returning data, identifying values or the number of rows affected by the stored procedure, and so on. The actual effect is that the return value can be used to determine the state of the stored procedure execution.

In fact, the program receives a return value regardless of whether the return value is provided. SQL Server will automatically return a 0 value when the stored procedure is completed by default.

In order to pass the return value from the stored procedure to the calling code, you only need to use the return statement.

RETURN [<integer value to return>]

It is important to note that the return value must be an integer .

The most important thing about the return statement is that it exits unconditionally from the stored procedure. Regardless of where you run to the stored procedure, no line of code will be executed after the return statement is called.

The unconditional here does not mean that the return statement will be executed wherever the code is executed. Instead, you can re-store the procedure with multiple return statements. These return statements are executed only when the standard conditional structure of the code issues a command. Once it happens, it cannot be returned.

Create a stored procedure as follows:

CREATE PROCSptestreturns as 　　 DECLARE @MyMessage nvarchar( -); DECLARE @MyOtherMessage nvarchar( -); SELECT @MyMessage = 'First return'; PRINT @MyMessage; RETURN; SELECT @MyOtherMessage = 'a second return'; PRINT @MyOtherMessage; RETURN;

Execute the stored procedure with the following output:

　　

In order to capture the value of the return statement, you need to assign the value to the variable in the EXEC statement. For example:

DECLARE @Return int EXEC @Return = Sptestreturns SELECT @Return

The output is as follows:

　　

It's simple but good. When run, you can see that the return statement terminates the code before running other code.

If you always return 0, then the execution is not successful, then we now rewrite the stored procedure above, let it return a specified value to indicate the state of execution.

CREATE PROCSptestreturns as 　　 DECLARE @MyMessage nvarchar( -); DECLARE @MyOtherMessage nvarchar( -); SELECT @MyMessage = 'First return'; PRINT @MyMessage; RETURN  -;--Change this to return 　　 SELECT @MyOtherMessage = 'a second return'; PRINT @MyOtherMessage; RETURN;

After execution, the following results are displayed:

　　

Vi. Advantages and disadvantages of stored procedures

The key benefits of stored procedures include the following:

• Enables processes that require process actions to be called
• Security
• Performance

　　1. Create a callable process

Many people are unaware of the need to make full use of stored procedures as a tool to implement security. Similar to views, you can create a stored procedure that returns a recordset without giving the user permission to access the underlying data table. Giving someone permission to execute a stored procedure means that they can perform any action in that stored procedure. However, assume that the action is performed in the context of the stored procedure.

　　2. Stored Procedures and performance

In general, stored procedures help improve system performance. However, if you design a stored procedure that lacks only, it makes the process you create very slow.

The stored procedure runs as follows:

　　

Run the CREATE proc procedure first. This resolves the query to ensure that the code is actually running. It differs from running scripts directly in that the CREATE PROC command can take advantage of so-called deferred name resolution. Deferred name resolution can ignore the fact that some objects do not yet exist.

After the stored procedure has been created, it waits for the first time to execute. At that time, the stored procedure was optimized, and the query plan was compiled and cached on the system. When you run the stored procedure several times, the cached query plan is used instead of creating a new query plan, unless specified by using the WITH RECOMPILE option. This means that each time the stored procedure is used, the stored procedure skips a lot of optimizations and compilation work. The exact time to save depends on the complexity of the batch, the size of the table in the batch, and the number of indexes on each table. Usually, the time saved is not much. But for most scenarios it could be 1 seconds or less-but this difference can be calculated by a percentage (1 seconds is 100% faster than 2 seconds). This difference becomes more pronounced when multiple invocations are required or in the case of loops.

　　3. Adverse aspects of stored procedures

The most important thing to realize about the disadvantage of stored procedures is that unless you manually intervene (using the WITH RECOMPILE option), the stored procedure is optimized only when the stored procedure is run for the first time, or when the tables involved in the query update the statistics.
This "one optimization, multiple use" strategy saves time on the stored procedure, but the strategy is also a double-edged sword. If the query is dynamic (that is, it was established when the EXEC command was used), the stored procedure is optimized only at the first run, but it will be discovered later. In short, the wrong plan may be used.

　　4. With RECOMPILE option

You can take advantage of the security Code and Code encapsulation provided by stored procedures, but ignore the impact of precompiled code. You can avoid the problem of not using the correct query plan, because you can ensure that a new schedule is created for a specific run. The method is to use the WITH RECOMPILE option.
There are two ways to use this option:

1. Can include with RECOMPILE at run time.

EXEC ' 1/1/2004 '  with RECOMPILE　　

This tells SQL Server to discard the existing execution plan and create a new plan-but only this time. That is, just this time using the WITH RECOMPILE option to execute the stored procedure.

You can also make it more durable by including the WITH RECOMPILE option in the stored procedure. If you use this method, the WITH RECOMPILE option is added before the AS statement in the CREATE PROC or ALTER PROC statement.

　　If you create a stored procedure with this option, the stored procedure will be recompiled each time you run it, regardless of what other options are selected at run time.

SQL Server Stored Procedures