SQL Server Transact-SQL Programming

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