BATCH STATEMENTS

BATCH STATEMENTS

A batch is a collection T-SQL statements can be written and executed as a single unit.

A batch may be

1.  Anonymous Batch/Name less Batch

2.  Named Batch

1. Anonymous Batch/Name less Batch: Set of T-SQL statements can written and executed as a single unit with out any proper name called Anonymous batch or Nameless batch.

Variable: Variable is nothing but a memory element, which can be varied through out the program execution.

Types of variable: In SQL Server there existed two types of variables.

1. Global Variables: These are the variables which can be accessible by any user,

any database, any function, any procedure. These variables are preceded by a special symbol ‘@@’.These are also known as “Public variables”.

E.g.: SELECT @@SERVERNAME

        SELECT @@VERSION

        SELECT @@LANGUAGE

          ------------------------------

      ------------------------------------

2. Local Variables: These are the variables which can be accessible by particular user, particular database, particular function, and particular procedure. These variables are preceded by a special symbol ‘@’.These are also known as “Private variables”.

Syntax to declare the local variables:

DECLARE @VARIABLENAME DATATYPE,…………

E.g.:

DECLARE @X INT,@Y VARCHAR(20)

Syntax to assign the values:

SET @VARIABLENAME=VALUE/EXPRESSION

E.g.:

SET @X=125

SET @Y=’NRSTT’

SET @C=@A+@B

Print Statement: This statement is used for printing the output in result window

Syntax:

PRINT @VARIABLENAME/CONSTANT

E.g.:

PRINT @X

PRINT @Y

PRINT ‘SQLSERVER’

Comments: In SQL Server comment is nothing but a n un-executable statement we can make a single line / multiple lines as comments

-- for single line comment

/*------------

   ------------*/ for multiple line comments

PROGRAMMING CONSTRUCTS: Programming constructs are used to execute the set of SQL statements as a unit. These are also known as Control Structures.

1)      IF – ELSE

2)      BEGIN – END

3)      WHILE

IF- ELSE: It is used to execute the statements based on condition.

Syntax : IF <CONDITION>

                        BEGIN

                        < STATEMENT 1>

                        END

            ELSE

                        BEGIN

                        < STATEMENT 2>

                        END

If condition is true then statement1 is executed otherwise statement 2  will be executed

WHILE: If the condition is true then statements under while will be excluded repeatedly until the given condition false

Syntax:

 WHILE(CONDITION)

BEGIN

<STATEMENT1>

<STATEMENT1>

<STATEMENT1>

------------------

END

E.g.1:

DECLARE @A INT,@ INT,@C INT

SET @A=5

SET @B=10

SET @C=@A+@B

PRINT @C

E.g.2:

DECLARE @SI MONEY,@P MONEY,@N INT,@R FLOAT

SET @P=10000

SET @N=2

SET @R=0.5

SET @SI=@P*@N*@R/100

PRINT @SI

E.g.3:

DECLARE @A INT, @B INT,

SET @ A =5

            SET @ B =10

            IF(@A>@B)

PRINT ‘B IS BIG’

ELSE

PRINT ‘B IS BIG OR EQUAL TO A’

E.g.4:

DECLARE @A INT

SELECT @A=COUNT(*) FROM EMP

IF (@A>0)

PRINT ‘TABLE WILL HAVE RECORDS’

ELSE

PRINT ‘NO RECORD IN THE TABLE

E.g.5:

DECLARE @A VARCHAR(10)

SELECT @A= JOB FROM EMP WHERE EMPNO =7369

IF (@A=’CLERK’)

PRINT ‘EMPLOYEE WORKING AS CLERK

ELSE

PRINT ‘EMPLOYEE IS :’ +@A

E.g.6:

 DECLARE @N INT,

SET @ N =1

WHILE(@N<=10)

BEGIN

PRINT @N

SET @N = @N+1

END

E.g.7:  

DECLARE @N INT,

SET @ N =1

WHILE(@N<=10)

BEGIN

IF (@N/2=0)

PRINT @N

SET @N = @N+1

END