Shulou(Shulou.com)05/31 Report--

Today, I would like to share with you the relevant knowledge points of Sql Server stored procedure instance analysis. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look.

Return data from stored procedure-SQL Server | Microsoft official document

A stored procedure is a real script, or more accurately, a batch, but not exactly, it is stored in a database rather than in a separate file.

There are input parameters, output parameters and return values in the stored procedure.

First, create a stored procedure: CREATE PROC

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

In grammar, PROC is an abbreviation for PROCEDURE, and the two options have the same meaning. After naming the stored procedure, there is a list of parameters. Parameters are optional. The keyword AS is followed by the actual code.

CREATE PROCEDURE | PROC [[schema.] [VARYING] [=] [out [put]] [READONLY] [, [schema.] [VARYING] [=] [out [put] [READONLY] [,...]] [WITH RECOMPILE | ENCRYPTION | [EXECUTE AS {CALLER | SELF | OWNER |}] AS | EXTERNAL NAME.

Example of a simple stored procedure:

CREATE PROC spPerson AS SELECT * FROM Person

Execute the stored procedure:

EXEC spPerson1, declaration parameters

Declaring parameters requires the following parts of information: name, data type, default value, direction,

There is a simple set of rules for names.

It must start with the @ symbol (like a variable). In addition, the rules are the same as ordinary variable rules, except that spaces cannot be embedded.

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

Note that when declaring a type, you must also use the VARYING and OUTPUT options when declaring the CURSOR type parameter. Meanwhile, OUTPUT can be abbreviated to OUT.

The syntax is as follows:

@ parameter_name [AS] datatype [= default | NULL] [VARYING] [OUTPUT | OUT]

An example of a stored procedure that requires incoming parameters:

CREATE PROC spName @ Name nvarchar (50) AS SELECT Name FROM Person WHERE Name LIKE @ Name +'%'

Execute the stored procedure:

EXEC spName 'wine'; 2. Provide default value

In terms of default values, parameters are different from variables. In the same case, the variable is generally initialized to a null value, while the parameter is not. In fact, if you do not provide a default value, it is assumed that the parameter is required and an initial value is required when the stored procedure is called.

In order for the parameter to be optional, a default value must be provided. The method is to add the "=" symbol and the value as the default value after the data type before the comma. In this way, users of the stored procedure can decide not to provide a value for this parameter or provide their own value.

Create a stored procedure as follows:

CREATE PROC spName @ Name nvarchar (50) = NULL AS IF @ Name IS NOT NULL SELECT * FROM Person WHERE NAME = @ Name ELSE SELECT * FROM Person WHERE Id = 45

Execute the following statement:

EXEC spName EXEC spName 'wishful Dao Wolf'

The output is as follows:

3. Output parameters

A stored procedure that gets the OUTPUT parameter:

CREATE PROC InsertPerson @ Id int OUTPUT-- must be marked as OUTPUT AS INSERT INTO Person VALUES ('Liu Bei', 22 ^ 190 'unknown', 'unmarried', 'kindergarten', 'unknown', 4999999) SET @ Id = @ @ IDENTITY

Execute the stored procedure:

DECLARE @ Id int-- in fact, the name can be different when called, for example, @ Num,@i, and so on. EXEC InsertPerson @ Id OUTPUT-- notice that there is also an OUTPUTSELECT @ Id4 and a return value here. The return value must be an integer.

The return value can be used to determine the state of the stored procedure execution.

By default, SQL Server automatically returns a value of 0 when the stored procedure is completed.

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

RETURN []

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

With regard to the RETURN statement, the most important thing is to know that it exits unconditionally from the stored procedure. No matter where the stored procedure is run, no line of code will be executed after the return statement is called.

The following stored procedure returns a specified value to indicate the execution status.

CREATE PROC spTestReturns AS DECLARE @ MyMessage nvarchar (50); DECLARE @ MyOtherMessage nvarchar (50); SELECT @ MyMessage = 'first RETURN'; PRINT @ MyMessage; RETURN 100;-- change this to return 100 SELECT @ MyOtherMessage =' second RETURN'; PRINT @ MyOtherMessage; RETURN

After execution, the results are displayed as follows:

DECLARE @ Return intEXEC @ Return = spTestReturns / / the first RETURNSELECT @ Return / / returns 1005 and executes the stored procedure:

The following points need to be noted for calling stored procedures:

For the output parameters in the stored procedure declaration, the OUTPUT keyword is required.

As with declaring stored procedures, the OUTPUT keyword must be used when calling stored procedures. This notifies the SQL Server in advance of the special handling required for the parameters. It is important to note, however, that if you forget to include the OUTPUT keyword, there is no run-time error, but the output value is not passed into the variable (the variable is most likely NULL).

The variable assigned to the output does not need to have the same name as the internal parameter in the stored procedure.

The EXEC (or EXECUTE) keyword is required because a call to a stored procedure is not the first thing a batch does (if a call to a stored procedure is the first thing a batch does, you don't have to use EXEC).

6. WITH RECOMPILE option

You can take advantage of the security code and code encapsulation benefits 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 plan is created for a specific run. The method is to use the WITH RECOMPILE option.

There are two ways to use this option:

1. You can include WITH RECOMPILE at run time. This tells SQL Server to abandon the existing execution plan and create a new one-but only this once. That is, only this time use the WITH RECOMPILE option to execute the stored procedure.

EXEC spMySproc '1Acue 2004' WITH RECOMPILE

2. You can also make it more persistent by including the WITH RECOMPILE option in the stored procedure.

If you use this approach, you can add the WITH RECOMPILE option before the AS statement in the CREATE PROC or ALTER PROC statement. If a stored procedure is created with this option, it is recompiled each time the stored procedure is run, regardless of what other option is selected at run time.

2. Modify the stored procedure: ALTER PROCALTER PROC spPerson AS SELECT * FROM Person WHERE Id = 45 3. Delete the stored procedure: DROP PROCDROP PROC | PROCEDURE [;] 4. Common stored procedures 1. Sp_help: query table information sp_help Person

Look at a table with that information, constraints, stored procedures, custom functions, and so on.

2. Sp_helpdb: view database information sp_helpdb TestDataCenter

Of course, you can also display all the database information under the current database connection without parameters.

This picture contains almost all the information in the database. With this picture, it is easy to get information about a database.

3. Sp_helpindex: view information about indexes on a table or view sp_helpindex Person

Note that the table name is in the parameter, and the Person above is the table name, not the index name.

4. Sp_helpconstraint: view the constraint information on the table sp_helpconstraint Person

Note that the parameter is the table name.

5. Sp_helpfile: view the file information sp_helpfile TestDataCenter according to the logical name of the file

Note that the parameter is the logical name of the file. You can also output all the file information of the current database without parameters.

6. Sp_helpfilegroup: view the filegroup information sp_helpfilegroup 'PRIMARY' based on the filegroup name

The parameter name is the logical name of the filegroup, of course, without parameters, so that only the filegroup information of the current database is output.

The display results are as follows:

7. Sp_helptext: displays definitions of default values, unencrypted Transact-SQL stored procedures, user-defined Transact-SQL functions, triggers, computed columns, CHECK constraint lights, etc. Sp_helptext spName

What is returned? Is the defined code.

These are all the contents of the article "Sql Server stored procedure instance Analysis". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.