Pogramming in Sql Server

Basisdata
Overview
Batches
Control – of – flow statement

2
TSQL is mainly used to build the application logic.
SQL is a programming language which focuses on managing relational
databases.
Go is a special command that is a batch terminator

Batch is a set of T-SQL commands that get sent to SQL

Server in one network packet
Batches
A group of one or more Transact-SQL statements sentat the
same time from an application to SQL Server for execution
SQL Server compiles the statements of a batch into a
single executable unit, called an execution plan
Example:
Select * from Employees
Select * from Products
Go
SQL Server Processes a batch interactively or from afile
Compiles the statement of a batch into single executable unit
(execution plan) and executed one at a time
If there is syntax error, nothing in the batch will execute
Certain statement can be combined to create a batch and others
cannot combined.

5
Batches
A compile error, such as a syntax error, prevents the
compilation of the execution plan. Therefore, no
statements in the batch are executed.
A run-time error, such as an arithmetic overflow or a
constraint violation, has one of the following effects:
Most run-time errors stop the current statement and
the statements that follow it in the batch.
Some run-time errors, such as constraint violations,
stop only the current statement. All the remaining
statements in the batch are executed

6
Batches (example)
CREATE TABLE dbo.t3(a int) ;
INSERT INTO dbo.t3 VALUES (1) ;
INSERT INTO dbo.t3 VALUES (1,1) ;
INSERT INTO dbo.t3 VALUES (3) ;
SELECT * FROM dbo.t3;

7
Batches (Example)
First, the batch is compiled. The CREATE TABLE
statement is compiled, but because the table dbo.t3
does not yet exist, the INSERT statements are not
compiled.
Second, the batch starts to execute. The table is
created.The first INSERT is compiled and then
immediately executed. The table now has one row.
Then, the second INSERT statement is compiled.
The compilation fails, and the batch is terminated.
The SELECT statement returns one row.

9
Rules for Using Batches

CREATE DEFAULT, CREATE FUNCTION, CREATE

PROCEDURE, CREATE RULE, CREATE SCHEMA,
CREATE TRIGGER, and CREATE VIEW statements cannot
be combined with other statements in a batch. The C REATE
statement must start the batch. All other statements that follow in that
batch will be interpreted as part of the definition of the first CREATE
statement.

10
Variables
Use a variable to store a temporary value.
You can declare a variable using the DECLARE statement
Syntax:
DECLARE @variable_name data_type(@ symbol is used by the
query processor to identify variables)
Ex:
(1)DECLARE @Charge int
(2)DECLARE @myvar char(20)
SET @myvar = 'This is atest'
SELECT @myvar
GO
In transact SQL: local variable and global variable
Local variable :user define them and must begin with :@ sign
Global variable :declare by the server and have two @ sign (@@)preceding
their names.

12
Standard Identifiers
Standard identifiers can contain from one to 128 characters, including letters,
symbols (_, @, or #), and numbers. No embedded spaces are allowed in
standard identifiers.
Rules for using identifiers include:
The first character must be an alphabetic character of a-z or A-Z.
After the first character, identifiers can include letters, numerals, or the @, $,
#, or _.
Identifier names starting with a symbol have specialuses:
An identifier beginning with the @ symbol denotes a local variable
or parameter.
An identifier beginning with a pound sign (#) denotes a temporary
table or procedure.
An identifier beginning with a double pound sign(##) denotes a global
temporary object.

13
Local Variables
User-defined with DECLARE Statement
Assigned Values with SETor Select Statement

14
Global variable
@@version
@@servername
@@spid
@@procid
@@error
@@rowcount
@@connections, etc…

15
CAST and CONVERT
Converts an expression of one data type to another
CAST ( expression AS data_type [ ( length ) ])
SELECT 9.5AS Original, CAST(9.5 AS int)AS in_integer,
CAST(9.5 AS decimal(6,4))AS in_decimal;
CONVERT ( data_type [ ( length )] ,expression [ ,style ] )
SELECT 9.5 AS Original, CONVERT(int, 9.5) AS in_integer,
CONVERT(decimal(6,4), 9.5) AS in_decimal;

16
Printing Message
Using PRINT statement to display a userdefined message
or content variable on the screen
Ex: DECLARE @MyName char(50)
SELECT @MyName = ‘Coomar Chris’
PRINT @MyName

17
Comment entry
Use comment entries in the batches to write
description code.
Example:
/*……………comment………………..*/
--comment

18
Comment
In Line Comments
SELECT ProductName,
(UnitsInStock + UnitsOnOrder) AS Max-- Calculates inventory
,SupplierID
FROM Products

Block Comments
/*
** This code retrieves all rows of the products table
** and displays the unit price, the unit price increased
** by 10 percent, and thename of the product.
*/
SELECT UnitPrice, (UnitPrice * 1.1), ProductName
FROM Products

19
Dynamically Constructing Statements
Use EXECUTE with String Literals andVariables
Use When You Must Assign Value ofVariable at Execution
Time
Any Variables andTemporaryTables Last Only During
Execution

20
Using Transactions
Processed Like a Batch
Data Integrity Is Guaranteed
Changes to the Database Are Either Applied Togetheror
Rolled Back

21
Control-Of-Flow Language
Control the flow of execution of SQL
statement
Typically used when statements have to be conditionally
or repeatedly executed
They are :
IF…ELSE statement,
CASE statement,
WHILE statement

22
IF…ELSE statement
Used to execute SQL statement conditionally
Syntax: IF boolean expression
{sql statement/statement block}
[ELSE boolean expression
{sql statement/statement block}]

23
CASE construct
For situations where several conditions need to be
evaluated
Evaluates a list of condition and returns one of various
possible result
Syntax :
CASE
WHE N boolean_expressionTHEN expression 1
[WHEN boolean_expression THEN expression][….]
[ELSE expression]
END

24
CASE

For some example, read sql book online or

CASE (Transact-SQL).pdf

25
WHILE
Allow a set of T-SQL Statements to execute repeteadly as
long as the given condition holds true.
Syntax:
WHILE boolean_expression
{sql_statement/statement_block}
[BREAK]
{sql_statement/statement_block}
[CONTINUE]

26
WHILE

27
Thank you

28