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_addlinkedserverhttps://msdn.microsoft.com/en-us/library/ms190479.aspx. 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: 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_executesqlhttps://msdn.microsoft.com/en-us/library/ms188001.aspx?f=255&MSPPError=-2147217396. This has the advantage to allow to incorporate variables (eg: a database name), but the inconvenient to suppress the syntactic coloration, the autocompletion (IntelliSensehttps://msdn.microsoft.com/en-us/library/hcw1s69b.aspx?f=255&MSPPError=-2147217396) 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: @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 WHILEhttp://msdn.microsoft.com/en-us/library/ms178642.aspx 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 NEXThttp://msdn.microsoft.com/en-us/library/ms180169.aspx. However, this method is relatively slow and should be avoided when it's possiblehttp://sqlpro.developpez.com/cours/sqlserver/MSSQLServer-avoidCursor/. 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 References