Transact-SQL Programming

Transact-SQL is a non-procedural or 4th generation language, hence it is very easy to work with Transact-SQL but both of non procedures natures it is not possible to solve a complex query using the Transact-SQL programming language. Fetaures are provided in Transact-SQL that are collectively called Transact-SQL Progarmming.

DATA TYPE In Transact-SQL Programming

All the data types of Transact-SQL can be used in Transact-SQL Programming but the datatype “Cursor ” and “Tables” can be used only in Transact-SQL programming and they cannot be used with in SQL when creating the table.

1. Declareing Variables

  1. Declare @<variableName><Datatype>,@<variableName><Datatype>

Within Transact-SQL programming, to declare a variable you need to use the keyword “Declare”. Variables in Transact-SQL programming must be prefixed with @.

2. Assigning Values to Variables

To assign a value to variables in Transact-SQL programming you need to use the keyword “Set” and by using the keyword “set” you can assign a value to only one variable at a time.

  1. set @<variableName>=value

3. Printing Output

Within Transact-SQL programming if you want to print the ouput use the keyword as “Print” with a message that you want to print.

Print 'Enter the message that you want to print'

4. Start and End Block

In the Transact-SQL programming the the block starts with “begin” and ends with the “END”.

  1. Begin
  2. //Block of code
  3. end

For example:

WAP to print sum of 2 intergers

  1. Begin
  2. declare @a int,@b int,@c int
  3. set @a =40
  4. set @b=30
  5. set @c=@a +@b
  6. print 'sum is '+ cast(@c as varchar)
  7. End

Taking Input for Variables

In Transact-SQL programming there is no option to take input into variables from a keyword but you can take data available in the DB as input for variables and for this purpose you need to write a select statement with the following two syntax.

  1. Select @<variablename>=<Colname>,@<variableName>=<ColName>….
  2. From <tablename>[where Condition]

This type of select statement that takes data available in the table in the DB as input for variables must return a single row and if it returns multiple rows then you will get an error.

Control Statement

  1. IF Statement

    Syntax
    1. Begin
    2. If<Condition>
    3. <statement>
    4. Else
    5. <statement>
    6. End

  2. Else-If Statement

    Syntax
    1. Begin
    2. If<Condition>
    3. <statement>
    4. Else If<Condition>
    5. <statement>
    6. Else If<Condition>
    7. <statement>
    8. Else
    9. <statement>
    10. End

  3. Case Statement
    1. Case
    2. When <condition1> then <value1>
    3. When <condition2> then <value2>
    4. .
    5. .
    6. .
    7. .
    8. Else <val n>
    9. End
  4. Looping Control Statement

    While
    1. While <condition>
    2. Begin
    3. <statement>
    4. End
    For example:

    WAP to print the weekname of current system date.

    Using If else
    1. begin
    2. declare @day int
    3. set @day =datepart(dw,getdate())
    4. if @day=1
    5. print 'sunday'
    6. else if @day=2
    7. print 'Monday'
    8. else if @day=3
    9. print 'Tuesday'
    10. else if @day=4
    11. print 'wednesday'
    12. else if @day=5
    13. print 'thursday'
    14. else if @day=6
    15. print 'Friday'
    16. else
    17. print 'satday'
    18. end
    Using case statement
    1. begin
    2. declare @day int
    3. set @day =datepart(dw,getdate())
    4. select case @day
    5. WHEN 1 THEN 'SUNDAY'
    6. WHEN 2 THEN 'MONDAY'
    7. WHEN 3 THEN 'TuesDAY'
    8. WHEN 4 THEN 'WednesDAY'
    9. WHEN 5 THEN 'ThursDAY'
    10. WHEN 6 THEN 'FriDAY'
    11. else
    12. 'saturday'
    13. end
    14. end
    For example:

    WAP to print the number no from 1 to 10.
    1. Begin
    2. declare @n int
    3. set @n =1
    4. while @n <=10
    5. begin
    6. print @n
    7. set @n =@n +1
    8. end
    9. end
    For example:

    WAP to Print Even no between 1 and 100