T-SQL Command Reference

Local Variable Declaration

DECLARE @X  INT
DECLARE @A INT, @B INT, @C CHAR(10)

A local variable is initially assigned a NULL value. A value can be assigned to a local variable by using the SET or SELECT statement.

DECLARE @X INT
SET @X = 1
SELECT @X = COUNT(*) FROM db.dbo.books

IF ... ELSE statement

DECLARE @X INT
SET @X = 1
IF @X = 1 PRINT 'X is 1'
ELSE PRINT 'X is NOT 1'

use Northwind
IF db_name() = 'Northwind'
  BEGIN
    PRINT 'Using Northwind Database'
    PRINT 'Second Statement'
  END

WHILE Statement

DECLARE @C INT
SET @C = 0
WHILE @C < 2
BEGIN
  PRINT 'C is ' + cast(@C as CHAR)
  SET @C = @C + 1
END

BREAK and CONTINUE statements
The BREAK statement exits out of the inner most WHILE loop, and the CONTINUE statement skips executing the rest of the statements between the CONTINUE and the END statement of the current loop.

WHILE TRUE
  BEGIN
    BREAK
  END

WHILE (@I <10)
  BEGIN
    PRINT 'I is ' + @I
    IF (@I >3) CONTINUE
  END

GOTO Statement

WHILE TRUE
  BEGIN
    GOTO ONE
  END

ONE:
PRINT 'Out of the Loop'

CASE Statement
The CASE statement allows you to replace a column value with a different value based on the original value. For example, a table column named gender may contain a value 0 representing female, and 1 representing male. The CASE statement allows you to translate value 0 to 'Female' and 1 to 'Male'.

SELECT Name,
  CASE
    when gender = 0 then 'Female'
    when gender = 1 then 'Male'
    else 'Unknown'
  END as "Gender"
  from db.dbo.Customer

Create Table/ Drop Table

create table MYTABLE (id int, name varchar(10), value varchar(100))
drop table MYTABLE
Tags: 

Comments

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.