Microsoft SQL Server/Stored procedures
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
Example of join between two servers:
select * from table1 t1 inner join [server2].[base2].[dbo].[table2] t2 on t2.id = t1.t2_id
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:
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:
@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
This command displays something in the Messages tab, by opposition to
SELECT which fills the Results tab.
print 'Hello World ! ' -- Displays "Hello World !" declare @n int set @n = 5 print 'the value is: ' + cast(@n as varchar)
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.
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).
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
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
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
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