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:

select *
from table1 t1
inner join [server2].[base2].[dbo].[table2] t2 on t2.id = t1.t2_id

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:

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 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
   

3. 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:

1. The private ones, introduced with Declare.
2. The arguments:

@StartDate varchar(8)       -- Mandatory argument
@EndDate varchar(8) = null  -- Optional argument
if @EndDate is null set @EndDate = convert(varchar,@StartDate + 1,112)
Declare @Name varchar(50)   -- Private variable

To create a new stored procedure:

CREATE PROCEDURE [dbo].[MyProcedure1]

To save an existing stored procedure:

ALTER PROCEDURE [dbo].[MyProcedure1]

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:

print 'Hello World ! ' -- Displays "Hello World !"

declare @n int
set @n = 5

print 'the value is: ' + cast(@n as varchar)

Conditions

IF

if @x=1 begin
  print 'x = 1'
end else if @x=2 begin
  print 'x = 2'
end else begin
  print 'x <> 1 et 2'
end

Remark: the begin and the end are optional.

CASE

set @Season = case 
 when @DayDate = '20110918' then 'summer'
 when @DayDate = '20110922' then 'autumn'
 else 'another season'
end

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):

declare @Column int = null
select Field1
from Table1
where Field1 = case when isnull(@Column,'')<>'' then @Column else Field1 end

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:

DECLARE @i int
WHILE @i <= 10
BEGIN
   UPDATE Table1
   SET Field2 = "petit" WHERE Field1 = @i
   SET @i = @i + 1
END

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:

USE Base1
declare @Name varchar(20)
DECLARE cursor1 CURSOR FOR SELECT FirstName FROM Table1
OPEN cursor1

/* First record from the selection */
FETCH NEXT FROM cursor1 into @Name
print 'Hello ' + @Name

/* Treatment of the other records in a loop */
while @@FETCH_STATUS = 0
  begin
    FETCH NEXT FROM cursor1 into @Name
    print 'Hello ' + @Name
  end

CLOSE cursor1;
DEALLOCATE cursor1;

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:

ALTER PROCEDURE [dbo].[MyProcedure1]
DECLARE	@result int
EXEC	@result = [dbo].[MyProcedure2] @Parameter1;
if @result = 0 begin
 ...
end

Exceptions

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

-- Transaction start
BEGIN TRAN
  BEGIN TRY
   -- Execution
   INSERT INTO Table1(Name1) VALUES ('ABC')
   INSERT INTO Table1(Name1) VALUES ('123')
   -- Transaction submission
   COMMIT TRAN
  END TRY
BEGIN CATCH
 -- Transaction cancellation if error
 ROLLBACK TRAN
END CATCH

Researches

To get the sp including a particular string:

SELECT name
FROM sysobjects syso
INNER JOIN syscomments sysc
ON syso.id = sysc.id
WHERE
(syso.xtype = 'P' or
syso.xtype = 'V')
AND
(syso.category = 0)
and text like '%String to search%'
group by name