# Microsoft SQL Server/Stored procedures
## Introduction

A stored procedure (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 sp_addlinkedserver.
Example of join between two servers:

## Syntax

The Microsoft T-SQL language provides a few improvements from the SQL standard:
* 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 SET QUOTED_IDENTIFIER ON.
* In SSMS, a SQL request can be executed in three ways:
* 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.
* By storing it in a string variable, before executing it with sp_executesql. 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: DECLARE @Request1 NVARCHAR(MAX) DECLARE @MyTable1 NVARCHAR(MAX) SET @MyTable1 = SET @Requst1 = 'SELECT * FROM ' + @MyTable1 EXECUTE sp_executesql @Request1
* By executing a procedure stored in a database, which contains the request. Eg: EXEC [MaBase1].[dbo].[MyProcedure1]
This call can be followed by arguments, like the imperative programming procedures.
Indeed, there a two sorts of variables in the stored procedures:
* The private ones, introduced with Declare.
* 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 AS + 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 GO.
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 SELECT which fills the Results tab.
Examples:

### Conditions
#### IF

    Remark: the begin and the end are optional.

#### CASE

To add a WHERE 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 where Field1 = isnull(@Column, Field1).

### Loops
#### 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 INTO, and reinitialized after the NEXT. 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:

## References

* ↑ https://msdn.microsoft.com/en-us/library/ms190479.aspx
* ↑ https://msdn.microsoft.com/en-us/library/ms188001.aspx?f=255&MSPPError=-2147217396
* ↑ https://msdn.microsoft.com/en-us/library/hcw1s69b.aspx?f=255&MSPPError=-2147217396
* ↑ http://msdn.microsoft.com/en-us/library/ms178642.aspx
* ↑ http://msdn.microsoft.com/en-us/library/ms180169.aspx
* ↑ http://sqlpro.developpez.com/cours/sqlserver/MSSQLServer-avoidCursor/