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)
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
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
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
-- 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
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
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.
