Stored Procedures in SQL Server

SQL Server Stored Procedure

Differences between Alter Procedure and Create Procedure:

1. Alter Proc expects to find an existing stored procedure, while Create does not.

2. Alter Proc retains any permissions that have been created in the stored procedure. It retains the same object ID in the System Object and allows the dependency to be retained.

3. Alter Proc retains any dependency information on other objects that may call the modified stored procedure.

Pay attention to the following points when using stored procedures and calling scripts:

1. The OUTPUT parameter in the Stored Procedure declaration must use the OUTPUT keyword.

2. Like declaring a stored procedure, the OUTPUT keyword must be used when calling the stored procedure. In this way, the SQL Server is notified in advance that special processing is required for the parameters. However, you must note that if you forget the keyword containing OUTPUT, there will be no running error or any message about it, however, the value of the output parameter is not passed into the variable (the resulting value may be NULL ). This means that unexpected results are obtained.

3. Variables assigned to the output result do not need to have the same name as internal parameters in the stored procedure.

4. the EXEC keyword is required, because calling a stored procedure is not the first thing to do for Batch Processing (if the first thing to do is batch processing when the stored procedure is called, then you can not use EXEC ).

Three common error types in SQL Server:

1. An error occurs during running and the code is terminated to continue running.

2. SQL Server knows but does not generate a running error that causes the code to stop running. Such errors are also called inline errors ).

3. errors that are more logical but do not attract much attention in SQL Server.

Advantages of stored procedures:

1. Generally, better performance is achieved (with exceptions ).

2. It is usually used as a security isolation layer (the method for controlling database access and updating ).

3. reusable code.

4. Code regionalization (business logic can be encapsulated)

5. flexible execution based on the dynamic process established during running.

Disadvantages of stored procedures:

1. Migration between platforms is no longer possible (for example, Oracle has a completely different implementation method for stored procedures ).

2. In some cases, the execution plan may be in the wrong state (which actually affects the performance ).