Transac sql

Transact SQL & Stored Procedure
How to code in Microsoft SQL Server

Training Division New Delhi

What is Transact-SQL?
• Programming language for SQL Server
– Standard database manipulationlanguage
• SELECT, INSERT, UPDATE • CREATE TABLE, CREATE PROCEDURE

– Use of variable in SQL statement
• Select @fname =firstname from customer_data

– Program flow Control such as
• IF-ELSE ,CASE, WHILE LOOPS

• Can write dynamic SQL to perform the same function as a procedural programming language do. • ANSI 92 Compliant with Extensions

Why would I use it?
• Reporting
– Letserver do the Computation – Only return final data for report/Program

• Business Logic • Batch processing • Server side processing

Where do I write the code?
• • • • ISQL/OSQL (script as a textfile) Query Analyzer Stored Procedure (Database) Triggers ( tables)

Transact-SQL Language
• • • • Data retrieval and modification (DML) Data definition language (DDF) Variables Control of flowData Definition Language
• Language to create Database – Create Database Mydb – Alter Database mydb – Drop Database mydb • Language to create tables, indexes, etc. – CREATE TABLE customer_data(cutomer_id samllint, first_name char(20), last_name char(20), phone char(10)) – ALTER table – Drop Table

Data Retrieval and Modification
• Retrieval
– SELECT
• • • • JOINS and FROM WHERE GROUP BY,HAVING, and Aggregate Functions ORDER BY

– PRINT
• declare @xx char(10) • select @xx =first_name from customer_data where customer_id=1234 • print @xx
• PRINT ‘Hello World’ • PRINT ‘My SQL ServerVersion is ‘+ @@version

Data Retrieval and Modification
• Modification
– INSERT – UPDATE – DELETE

Variables
• Support Two type of Variable.

• Local Variable
– Scope is limited to currentbatch – Special names
• All variable names must begin with @.

– All variables must be Declared
• DECLARE @X INTEGER • DECLARE @XX AS CHAR(20)

– Setting variables is just like retrieving...