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