Understanding stored procedures in SQL Server

The stored procedure of the database is understood as, the subroutine that processes the data, writes like function, uses up like function, in SQL Server the stored procedure divides into two big classes, the system and the custom, the system all puts in the Master System database, the custom is writes itself, uses the DDL the related statement to be able, Creating a stored procedure, like a function, can have a parameter, or it can be without a parameter, and it is simple to write without a reference:

CREATE PROC Hello  as    BEGIN        /*.... *    /ENDGO

When executing, you just need to execute it in your custom stored procedure.

EXEC Hello

When you have a reference, you are not a stranger:

CREATE PROC @n1 int  as    BEGIN        // .....     END GO EXEC ' 123 ' EXEC @n2 = ' 123 ' * *

If the first statement of the batch is executed, the exec can be omitted

At the time of writing, there will be many different requirements, the stored procedure does not return the value, we need to return the relevant information when we want to be similar to the same-C + + to pass the pointer or reference, in SQL Server is implemented by adding output after the variable.

There are a number of properties that are decorated with parameters:

VARYING	Applies only to the cursor variable for the output parameter, which is dynamically constructed by the stored procedure
Parameter = value	Represents the default value
READONLY	Modifying parameters that cannot be updated or modified in a stored procedure, similar to the const
RECOMPILE	Recompile when executing stored procedures

Precautions:

Custom stored procedures can only be created in the current database except temporary stored procedures, temporary stored procedures are created in the system database tempdb

After the stored procedure is created successfully, the procedure name is in sysobjects and the statement text is stored in syscomment

Automatic execution of stored procedures is a number of stored procedures that SQL Server automatically executes during startup, and these stored procedures cannot have parameters.

Understanding stored procedures in SQL Server