stored procedures in SQL Server

stored procedures for SQL Server

The difference between Alter procedure and create procedure:

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

2. Alter proc retains any permissions that have been established on the stored procedure. It retains the same object ID in the system object and allows for the retention of dependencies.

3. Alter proc retains any dependency information on other objects that might invoke the modified stored procedure.

A few things to note about the stored procedure itself and how the calling script uses it:

1. For output parameters in stored procedure declarations, use the OUTPUT keyword.

2. As with declaring stored procedures, you must use the OUTPUT keyword when invoking a stored procedure. This gives you an advance notice of SQL Server, telling it that the parameters require special processing. However, it is important to note that if you forget the keyword that contains output, you will not generate a run-time error and you will not get any messages about it, but the value of the output parameter will not be passed into the variable (most likely a null value). This means getting unexpected results.

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

4. The EXEC keyword is necessary because a call to a stored procedure is not the first thing to do with a batch (exec is not used if the first thing a batch does when the call is made to a stored procedure).

3 common types of errors in SQL Server:

1. Error generating run-time error and terminating code to continue running.

2. SQL Server is aware of errors that do not produce run-time errors that cause code to stop running. This kind of error is also called an inline error (inline).

3. A more logical error that is less noticeable in SQL Server.

Advantages of stored procedures:

1. Better performance is usually available (with exceptions).

2. Typically used as a security isolation layer (control of database access and updates).

3. Reusable code.

4. Regionalization of code (can encapsulate business logic)

5. Flexible implementation based on dynamic processes established at run time.

Disadvantages of stored procedures:

1. No more platform porting (for example, Oracle has a completely different approach to the implementation of stored procedures).

2. In some cases it is possible to fall into the wrong execution plan (which actually affects performance).