Microsoft SQL Server/Stored procedures

Introduction
A (sp) is a group of SQL requests, saved into a database. In SSMS, they can be found just near the tables.

Actually in terms of software architecture, it's better to stored the T-SQL language into the database, because if a tier changes there would be no need to modify another.

Usually the stored procedures manipulate their database tables, however they can also interact with the other databases tables, even located on another server, called a linked server. To create a linked server:
 * In SSMS, click on the menu "Server objects", "Linked servers", and fill the account to use to connect.
 * In SQL, use.

Example of join between two servers:

Syntax
The Microsoft T-SQL language provides a few improvements from the SQL standard: This call can be followed by arguments, like the imperative programming procedures.
 * By default, quotation marks play a different role than apostrophes which serves to create strings of characters. To use them in the same way (for example to nest them), one should launch.
 * In SSMS, a SQL request can be executed in three ways:
 * 1) Directly in a blank window, visible when clicking on "New request". Then it's possible to save it in a .sql file, to be able to reopen it in the same window.
 * 2) By storing it in a string variable, before executing it with  . This has the advantage to allow to incorporate variables (eg: a database name), but the inconvenient to suppress the syntactic coloration, the autocompletion (IntelliSense ) and the SSMS debugging. Eg:
 * 3) By executing a procedure stored in a database, which contains the request. Eg:

Indeed, there a two sorts of variables in the stored procedures:
 * 1) The private ones, introduced with Declare.
 * 2) The arguments:

To create a new stored procedure:

To save an existing stored procedure: Ideally this instruction should be present at the sp beginning, followed by  + its name, so the code execution save it (and doesn't launch it). To get its result, SSMS offers the option on right click: "Execute the stored procedure...". This generates another SQL request, which opens in a new tab above the result, calling the stored procedure with its parameters.


 * Attention: SSMS doesn't tolerate a backup a stored procedure with compilation errors. So if the backup is urgent, just comment the code in error or create a temporary .sql.


 * Attention: the error messages communicate a line number which doesn't correspond to the SSMS lines. It's actually offset from the last.

Then, these sp can be called by programs in any programming language which provides a SQL Server driver, such as PHP or VB, and will present the results from a recordset variable.

PRINT
This command displays something in the Messages tab, by opposition to  which fills the Results tab.

Examples:

IF

 * Remark: the begin and the end are optional.

CASE
To add a  condition only if a value is present, the trick is to set in the other case something always true (eg: Field1 = Field1): The above example would be simpler with.

WHILE
The loop "while" uses a condition to stop, for example a counter:

CURSOR
A cursor allows to treat a recordset line by line, each stored in a variable mentioned after, and reinitialized after the. However, this method is relatively slow and should be avoided when it's possible.

For example, if one record treatment depends on the previous one, or to print some characters:

Execution of one stored procedure from another one
SSMS also provides a step by step execution mode (like in Visual Basic), by pressing F11 at each step it's possible to follow the variables values at the bottom left.

The break points are available too, to jump from one line to another.

Remark: in metaprogramming, no sp modification will be taken into account by the process during its execution.

To execute a sp from another:

Exceptions
Appeared with SQL Server 2005, the exceptions handling looks like this:

Researches
To get the sp including a particular string: