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

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.

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

Begin
//Block of code
end

For example:

WAP to print sum of 2 intergers

Begin
declare @a int,@b int,@c int
set @a =40
set @b=30
set @c=@a +@b
print 'sum is '+ cast(@c as varchar)
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.

Select @<variablename>=<Colname>,@<variableName>=<ColName>….
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
   

   Begin
   If<Condition>
   <statement>
   Else
   <statement>
   End

   
   
2. Else-If Statement
   
   Syntax
   

   Begin
   If<Condition>
   <statement>
   Else If<Condition>
   <statement>
   Else If<Condition>
   <statement>
   Else
   <statement>
   End
   

3. Case Statement
   

   Case
   When <condition1> then <value1>
   When <condition2> then <value2>
   .
   .
   .
   .
   Else <val n>
   End

4. Looping Control Statement
   
   While
   

   While <condition>
   Begin
   <statement>
   End

   For example:
   
   WAP to print the weekname of current system date.
   
   Using If else
   

   begin
   declare @day int
   set @day =datepart(dw,getdate())
   if @day=1
   print 'sunday'
   else if @day=2
   print 'Monday'
   else if @day=3
   print 'Tuesday'
   else if @day=4
   print 'wednesday'
   else if @day=5
   print 'thursday'
   else if @day=6
   print 'Friday'
   else
   print 'satday'
   end

   Using case statement
   

   begin
   declare @day int
   set @day =datepart(dw,getdate())
   select case @day
   WHEN 1 THEN 'SUNDAY'
   WHEN 2 THEN 'MONDAY'
   WHEN 3 THEN 'TuesDAY'
   WHEN 4 THEN 'WednesDAY'
   WHEN 5 THEN 'ThursDAY'
   WHEN 6 THEN 'FriDAY'
   else
   'saturday'
   end
   end

   For example:
   
   WAP to print the number no from 1 to 10.
   

   Begin
   declare @n int
   set @n =1
   while @n <=10
   begin
   print @n
   set @n =@n +1
   end
   end

   For example:
   
   WAP to Print Even no between 1 and 100