SQL: Basic Transact-SQL commands and syntax

This article is not suppose to make you become a Transact-SQL export, instead it is only suppose to make me don't look like a idiot when reading a SQL Stored Procedure. In that perspective I wrote this article which covered some basic but very often used SQL syntax and command.

1st of all, how to declare variables. The followed code declared a integer, a string and a record-set point:

DECLARE @Counter int
DECLARE @RegionName NVARCHAR(510)
DECLARE @RegionId TABLE (RegionId int)

2nd of al, let’s take a look how to create code block, a classic sample will be like this:

BEGIN TRY 
  BEGIN TRAN
    IF NOT EXIST (SELECT ...) –- make your condition here
    BEGIN
      -- do your stuff here 
    END
  COMMIT
END TRY
BEGIN CATCH
  IF @@Trancount >0
    ROLLBACK

  DECLARE @err VARCHAR(500)
  SET @err = ERROR_MESSAGE()
  RAISERROR (@err, 16, 1)
END CATCH

GO

Above is a complete cycle when you are trying to do some database operation with Transaction cooperation. We begin a TRY block first (if there is error the CATCH block will catch it), and inside the TRY block we can do anything we want, in this case, typically we put a IF BEGIN END block here. In the CATCH block we first try to ROLLBACK all the changes we made first by check the @@Trancount value to see if there is any Transaction started, and then we raise a error message out.

3rd of all, what does the GO sentence do? GO is not a Transact-SQL statement, it just simply signals the end of a batch of Transact-SQL statements. No any Transact-SQL statement can occupy the same line as a GO command but the comments.

In another word, GO marked a end of a T-SQL batch, after the GO any variable will be brand new and unknown. Here is a example from MSDN:

USE pubs
GO

DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO
-- @MyMsg is not valid after this GO ends the batch.

-- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg
GO

4th of all, a good stuff, SET IDENTITY_INSERT. Allows explicit values to be inserted into the identity column of a table. As you know after you set the table identity field to auto increasing integer you can insert a record with a particular id value any more. But by swtich on/off the IDENTITY_INSERT you can do it.

SET IDENTITY_INSERT MyTable ON
  -- Insert records with any id value you want
SET IDENTITY_INSERT MyTable OFF

5th of all, create and delete a table in T-SQL:

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO

-- Drop products table.
DROP TABLE products
GO 

6th of all, some useful methods you can use in your SQL:

* ISNULL ( check_expression , replacement_value ) , Replaces NULL with the specified replacement value.
* COALESCE ( expression [ ,...n ] ) , Returns the first non null expression among its arguments.
* GETDATE, Returns the current system date and time in the SQL Server standard internal format for date-time values.
…


Then followed is a example to show you how to change current existed table's structure.

If NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='HighlightBox'
                and COLUMN_NAME='EndPrice' )
BEGIN
  ALTER TABLE HighlightBox ADD EndPrice decimal(10,2) NULL
  ALTER TABLE HighlightBox WITH CHECK ADD CONSTRAINT FK_HighlightBox_TicketAvailabilityID
        FOREIGN KEY(TicketAvailabilityID)
        REFERENCES Ticket_Availability (Ticket_Availability_Id)
END
GO

Finally, there are some rules from MSDN apply to T-SQL batches:

* CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must begin the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.

* A table cannot be altered and then the new columns referenced in the same batch.

* If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch. Security Note Batch files may contain credentials stored in plain text. Credentials may be echoed to the user's screen during batch execution.