SQL Server/T-SQL Tutorial/Transact SQL/Code Block

Материал из SQL эксперт
Перейти к: навигация, поиск

Begin...end block

   <source lang="sql">

4> 5> 6> CREATE TABLE Orders ( 7> OrderID int IDENTITY (1, 1) NOT NULL , 8> CustomerID nchar (5) NULL , 9> EmployeeID int NULL , 10> OrderDate datetime NULL , 11> RequiredDate datetime NULL , 12> ShippedDate datetime NULL , 13> ShipVia int NULL , 14> Freight money NULL DEFAULT (0), 15> ShipName nvarchar (40) NULL , 16> ShipAddress nvarchar (60) NULL , 17> ShipCity nvarchar (15) NULL , 18> ShipRegion nvarchar (15) NULL , 19> ShipPostalCode nvarchar (10) NULL , 20> ShipCountry nvarchar (15) NULL) 21> GO 1> 2> create PROC spInsertDateValidatedOrder 3> @CustomerID nvarchar(5), 4> @EmployeeID int, 5> @OrderDate datetime = NULL, 6> @RequiredDate datetime = NULL, 7> @ShippedDate datetime = NULL, 8> @ShipVia int, 9> @Freight money, 10> @ShipName nvarchar(40) = NULL, 11> @ShipAddress nvarchar(60) = NULL, 12> @ShipCity nvarchar(15) = NULL, 13> @ShipRegion nvarchar(15) = NULL, 14> @ShipPostalCode nvarchar(10) = NULL, 15> @ShipCountry nvarchar(15) = NULL, 16> @OrderID int OUTPUT 17> AS 18> DECLARE @Error int 19> DECLARE @BadDate varchar(12) 20> DECLARE @InsertedOrderDate smalldatetime 21> IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7 OR @OrderDate IS NULL 22> BEGIN 23> SELECT @BadDate = CONVERT(varchar, @OrderDate) 24> RAISERROR (60000,1,1, @BadDate) WITH SETERROR 25> RETURN @@ERROR 26> END 27> SELECT @InsertedOrderDate = 28> CONVERT(datetime,(CONVERT(varchar,@OrderDate,112))) 29> PRINT "The Time of Day in Order Date was truncated" 30> INSERT INTO Orders VALUES(@CustomerID,@EmployeeID,@InsertedOrderDate,@RequiredDate, 31> @ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity, 32> @ShipRegion,@ShipPostalCode,@ShipCountry) 33> SELECT @Error = @@ERROR 34> IF @Error != 0 35> BEGIN 36> IF @Error = 547 37> BEGIN 38> PRINT "Supplied data violates data integrity rules" 39> PRINT "Check that the supplied customer number exists" 40> PRINT "in the system and try again" 41> END 42> ELSE 43> BEGIN 44> PRINT "An unknown error occurred. Contact your System Administrator" 45> PRINT "The error was number " + CONVERT(varchar, @Error) 46> END 47> RETURN @Error 48> END 49> SELECT @OrderID = @@IDENTITY 50> RETURN 51> GO 1> 2> 3> drop PROC spInsertDateValidatedOrder; 4> drop table Orders; 5> GO</source>


When multiple lines of code follow an IF statement it is best to wrap the lines in a BEGIN. . . END block.

   <source lang="sql">

7> 8> 9> CREATE PROCEDURE spTableExists 10> @TableName VarChar(128) 11> AS 12> IF EXISTS(SELECT * FROM sysobjects WHERE name = @TableName) 13> BEGIN 14> PRINT @TableName + " exists" 15> PRINT @TableName + " exists" 16> END 17> ELSE 18> PRINT @TableName + " does not" 19> GO 1> 2> drop proc spTableExists; 3> GO</source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Transact_SQL/Code_Block&oldid=6337»