SQL Server/T-SQL Tutorial/Transact SQL/Code Block
Begin...end block
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
When multiple lines of code follow an IF statement it is best to wrap the lines in a BEGIN. . . END block.
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