sql server 2000 research series - transact sql

Technical Integration DepartmentTechnical Integration DepartmentSystem Analysis & Training GroupSystem Analysis & Training Group

Jerry YangJerry YangAugust 4, 2005August 4, 2005

SQL Server 2000 Research Series SQL Server 2000 Research Series

Transact-SQLTransact-SQL

IntroductionIntroduction Stored Procedure Designing Concepts Stored Procedure Designing Concepts Transact-SQL Programming ConstructsTransact-SQL Programming Constructs Basic Knowledge of Function Basic Knowledge of Function SummarySummary

AgendaAgenda

IntroductionIntroduction Stored Procedure Designing Concepts Stored Procedure Designing Concepts Transact-SQL Programming ConstructsTransact-SQL Programming Constructs Basic Knowledge of FunctionBasic Knowledge of Function SummarySummary


What Is SQL?What Is SQL? Created In Mid-1970s Created In Mid-1970s Used In Commercial ProductUsed In Commercial Product SQLSQL

SStructuredtructured EEnglishnglish QUEQUEryry LLanguageanguage SStructuredtructured QQueryuery LLanguageanguage

What Is ANSI SQL?What Is ANSI SQL? ANSI SQL-89ANSI SQL-89 ANSI SQL-92ANSI SQL-92 ANSI / ISO SQL-99ANSI / ISO SQL-99 ANSI / ISO SQL-2003ANSI / ISO SQL-2003

IntroductionIntroduction

: : IBMIBM :: OracleOracle

(SQL1)(SQL1)(SQL2)(SQL2)

(SQL3)(SQL3)(SQL-200n)(SQL-200n)

What Is Transact-SQL?What Is Transact-SQL? Sybase & Microsoft SQL ServerSybase & Microsoft SQL Server Extension of SQL Programming LanguageExtension of SQL Programming Language

IntroductionIntroduction

IntroductionIntroduction Stored Procedure Designing ConceptsStored Procedure Designing Concepts Transact-SQL Programming ConstructsTransact-SQL Programming Constructs Basic Knowledge of FunctionBasic Knowledge of Function SummarySummary


DefinitionDefinition Stored procedures are batches that SQL Server Stored procedures are batches that SQL Server

stores in the database and then executes by stores in the database and then executes by name.name.

Why Use Stored Procedure?Why Use Stored Procedure? Performance IssuesPerformance Issues Centralized MaintenanceCentralized Maintenance Data Integrity ConsiderationData Integrity Consideration

Stored Procedure Designing ConceptsStored Procedure Designing Concepts

Types of Stored ProceduresTypes of Stored Procedures User-defined Stored ProcedureUser-defined Stored Procedure System Stored ProcedureSystem Stored Procedure TriggerTrigger ViewView User-defined FunctionUser-defined Function ETC.ETC.


User-defined Stored ProcedureUser-defined Stored Procedure Database SelectionDatabase Selection


User-defined Stored Procedure User-defined Stored Procedure (Continued)(Continued) Stored Procedure CreationStored Procedure Creation

CREATE PROCEDURE Name_ListCREATE PROCEDURE Name_List

AS SELECT * AS SELECT *

FROM CustomerFROM Customer

Stored Procedure DeletionStored Procedure Deletion DROP PROCEDURE Name_ListDROP PROCEDURE Name_List

LimitsLimits Maximum Length of Name – 128 CharactersMaximum Length of Name – 128 Characters Maximum Size of Stored Procedure – 128 MBMaximum Size of Stored Procedure – 128 MB


Contents

Stored Procedure Name

User-defined Stored Procedure (Continued)User-defined Stored Procedure (Continued) ParametersParameters

Input ParametersInput Parameters

CREATE PROCEDURE GetName_MultipleCREATE PROCEDURE GetName_Multiple

@in_Customer_ID@in_Customer_ID INTINT

ASAS SELECT nameSELECT name


WHERE Customer_ID <= WHERE Customer_ID <= @in_Customer_ID@in_Customer_ID

ExampleExample EXEC GetName_Multiple 10EXEC GetName_Multiple 10


User-defined Stored Procedure (Continued)User-defined Stored Procedure (Continued) Parameters (Continued)Parameters (Continued)

Output ParametersOutput Parameters

CREATE PROCEDURE GetName_SingleCREATE PROCEDURE GetName_Single

@in_Customer_ID@in_Customer_ID INT,INT,

@out_Name char(20) OUTPUT@out_Name char(20) OUTPUT

ASAS SELECT @out_Name = nameSELECT @out_Name = name


WHERE Customer_ID = @in_Customer_IDWHERE Customer_ID = @in_Customer_ID

ExampleExample

Declare @Result_Name varchar(20)Declare @Result_Name varchar(20)

EXEC GetName_Single 125, @Result_Name OUTPUTEXEC GetName_Single 125, @Result_Name OUTPUT

SELECT @Result_Name SELECT @Result_Name


Data TypesData Types Four Basic Data Type CategoriesFour Basic Data Type Categories

CHARACTERCHARACTER NUMERICNUMERIC DATE & TIMEDATE & TIME MISCELLANEOUSMISCELLANEOUS

Transact-SQL Programming Constructs Transact-SQL Programming Constructs

Data TypesData Types CHARACTERCHARACTER

charchar• Usage: Usage: Declare @Emp_No char(50)Declare @Emp_No char(50)

varcharvarchar• Usage : Usage : Declare @Emp_No varchar(50)Declare @Emp_No varchar(50)

texttext• Usage : Usage : Declare @Emp_No textDeclare @Emp_No text• Maximum Storage: Maximum Storage: 2 Giga Bytes2 Giga Bytes


Data Types (Continued)Data Types (Continued) CHARACTER (Continued)CHARACTER (Continued)

nnchar (char (NNational char)ational char)• Usage: Usage: Declare @Emp_No nchar(50)Declare @Emp_No nchar(50)

nnvarchar (varchar (NNational char varying)ational char varying)• Usage: Usage: Declare @Emp_No nvarchar(50)Declare @Emp_No nvarchar(50)

nntext (text (NNational text)ational text)• Usage: Usage: Declare @Emp_No ntextDeclare @Emp_No ntext• Maximum Storage: Maximum Storage: 11 Giga BytesGiga Bytes


UnicodeUnicode

Data Types (Continued)Data Types (Continued) NUMERICNUMERIC

intint• Usage: Usage: Declare @Salary intDeclare @Salary int• Storage Size: Storage Size: 4 Bytes4 Bytes• Range: Range: -2,147,483,648 (-2G) ~ 2,147,483,647 (2G - 1)-2,147,483,648 (-2G) ~ 2,147,483,647 (2G - 1)

smallintsmallint• Usage: Usage: Declare @Salary smallintDeclare @Salary smallint• Storage Size: Storage Size: 2 Bytes2 Bytes• Range: Range: -32,768 (-32K) ~ 32,767 (32K - 1)-32,768 (-32K) ~ 32,767 (32K - 1)

tinyinttinyint• Usage: Usage: Declare @Age tinyintDeclare @Age tinyint• Storage Size: Storage Size: 1 Byte1 Byte• Range: Range: 0 ~ 2550 ~ 255


Data Types (Continued)Data Types (Continued) NUMERIC (Continued)NUMERIC (Continued)

bigintbigint• Usage: Usage: Declare @Salary bigintDeclare @Salary bigint• Storage Size: Storage Size: 8 Bytes8 Bytes• Range: Range: -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807

bitbit• Usage: Usage: Declare @Validation bitDeclare @Validation bit• Storage Size: Storage Size: 1 Bit1 Bit• Range: Range: 0 ~ 10 ~ 1

Decimal, Float, Money, Numeric, ETC.Decimal, Float, Money, Numeric, ETC.


Data Types (Continued)Data Types (Continued) DATE & TIMEDATE & TIME

datetimedatetime• Usage: Usage: Declare @Emp_Birthday datetimeDeclare @Emp_Birthday datetime• Storage Size: Storage Size: 8 Bytes8 Bytes• Example: Example: 2005-08-03 11:10:53 .3902005-08-03 11:10:53 .390

smalldatetimesmalldatetime• Usage: Usage: Declare @Emp_Birthday smalldatetimeDeclare @Emp_Birthday smalldatetime• Storage Size: Storage Size: 4 Bytes4 Bytes• Example: Example: Jan 1, 2005 12:10:00Jan 1, 2005 12:10:00


VariablesVariables Local VariablesLocal Variables

DeclarationDeclaration• @ : Prefix For Local Variables@ : Prefix For Local Variables

Example: Example: Declare @Emp_FirstName varchar(50)Declare @Emp_FirstName varchar(50)

UsageUsage• Assigning Value With SELECT StatementAssigning Value With SELECT Statement

Example: Example: SELECT @Emp_FirstName = ‘Smith’SELECT @Emp_FirstName = ‘Smith’

• Assigning Value With SET StatementAssigning Value With SET Statement

Example: Example: SET @Emp_FirstName = ‘Smith’SET @Emp_FirstName = ‘Smith’


Variables (Continued)Variables (Continued) Local Variables (Continued)Local Variables (Continued)

Usage (Continued)Usage (Continued)• Display The Values of VariablesDisplay The Values of Variables

Example: Example: SELECT @Emp_FirstNameSELECT @Emp_FirstName

PRINT @Emp_FirstNamePRINT @Emp_FirstName


Variables (Continued)Variables (Continued) GlobalGlobal VariablesVariables

DeclarationDeclaration• @@ : Prefix For Global@@ : Prefix For Global VariablesVariables

Example: Example: @@ERROR@@ERROR

ExampleExample• INSERT INTO Employee VALUES(1, ‘John’, ‘Young’ ) ;INSERT INTO Employee VALUES(1, ‘John’, ‘Young’ ) ;

SELECT @@ERRORSELECT @@ERROR


Flow-Control StatementsFlow-Control Statements CommentsComments

Single-Line Comments: --Single-Line Comments: --• Example: Example:

-- The following codes were designed by John.-- The following codes were designed by John.

Multiple-Line Comments: /* */Multiple-Line Comments: /* */• Example: Example:

::

/* The comment starts here!/* The comment starts here!

::

::

The comment ends here! */The comment ends here! */

::


Flow-Control Statements (Continued)Flow-Control Statements (Continued) Statement BlocksStatement Blocks

CREATE PROCEDURE ShowNames AS CREATE PROCEDURE ShowNames AS

BEGINBEGIN

SELECT Name FROM EmployeeSELECT Name FROM Employee

ENDEND


Flow-Control Statements (Continued)Flow-Control Statements (Continued) Conditional Execution – IF StatementConditional Execution – IF Statement

CREATE PROCEDURE Order_Handler AS CREATE PROCEDURE Order_Handler AS

BEGINBEGIN

IF @ErrorCode = 0IF @ErrorCode = 0

BEGINBEGIN

INSERT INTO Order (Order_Date, Goods)INSERT INTO Order (Order_Date, Goods)

VALUES (‘2005/01/01, ‘Camera’)VALUES (‘2005/01/01, ‘Camera’)

SELECT @Status = @@ErrorSELECT @Status = @@Error

ENDEND

ELSEELSE

::

ENDEND


Flow-Control Statements (Continued)Flow-Control Statements (Continued) Looping – WHILE StatementLooping – WHILE Statement

CREATE PROCEDURE Order_Handler AS CREATE PROCEDURE Order_Handler AS BEGINBEGIN

::WHILE @Counter <= 100WHILE @Counter <= 100BEGINBEGIN

INSERT INTO Order (Order_ID, Goods)INSERT INTO Order (Order_ID, Goods)VALUES (@Counter, ‘Camera’)VALUES (@Counter, ‘Camera’)

SELECT @Counter = @ Counter + 1SELECT @Counter = @ Counter + 1ENDEND

::ENDEND


Types of FunctionsTypes of Functions Build-in FunctionsBuild-in Functions

Distinguished by The Type of ResultDistinguished by The Type of Result ScalarScalar

• Definition:Definition: Scalar functions refers to the fact that these Scalar functions refers to the fact that these functions return only one value.functions return only one value.

• Example:Example: getdate()getdate(), , IsNumeric(‘TEST’)IsNumeric(‘TEST’) AggregateAggregate

• Definition:Definition: Aggregate functions perform an operation on Aggregate functions perform an operation on a set of records and return a single value.a set of records and return a single value.

• Example:Example: SELECT Avg(salary) FROM EmployeeSELECT Avg(salary) FROM Employee RowsetRowset

• Definition:Definition: Rowset Functions return a complete Rowset Functions return a complete recordset to the caller.recordset to the caller.

• Example:Example: OpenQuery(Northwind, ‘select * from orders’)OpenQuery(Northwind, ‘select * from orders’)

Basic Knowledge of FunctionBasic Knowledge of Function

Linked Server NameLinked Server Name

Types of Functions (Continued)Types of Functions (Continued) User-defined Functions (Suspended)User-defined Functions (Suspended)

Basic Knowledge of FunctionBasic Knowledge of Function

What Do You Need To Know Today…What Do You Need To Know Today… IntroductionIntroduction

SQL, ANSI SQL, Transact-SQLSQL, ANSI SQL, Transact-SQL

Stored Procedure Designing ConceptsStored Procedure Designing Concepts Stored Procedure TypesStored Procedure Types Stored Procedure StructureStored Procedure Structure

Transact-SQL Programming ConstructsTransact-SQL Programming Constructs Data Types – Character, Numeric, Date & TimeData Types – Character, Numeric, Date & Time Variables – Local Variable, Global VariableVariables – Local Variable, Global Variable Flow Control Statements – Block, IF…Then, While …Flow Control Statements – Block, IF…Then, While …

Basic Knowledge of FunctionBasic Knowledge of Function Build-in FunctionsBuild-in Functions User-defined FunctionsUser-defined Functions

SummarySummary

Fundamentals of Database SystemsFundamentals of Database Systems Author:Author: Elmasri / NavatheElmasri / Navathe Publisher:Publisher: Addison-Wesley Publishing CompanyAddison-Wesley Publishing Company

Inside of Microsoft SQL Server 2000Inside of Microsoft SQL Server 2000 Author:Author: Kalen DelaneyKalen Delaney Publisher:Publisher: Microsoft PressMicrosoft Press

SQL Server 2000 – Stored Procedure & XML ProgrammingSQL Server 2000 – Stored Procedure & XML Programming Author:Author: Dejan SundericDejan Sunderic Publisher:Publisher: Brandon A. NordinBrandon A. Nordin

Transact-SQL ProgrammingTransact-SQL Programming Author:Author: Kevin Kline, Lee Gould, Andrew ZanevskyKevin Kline, Lee Gould, Andrew Zanevsky Publisher:Publisher: O’REILLYO’REILLY

WWW.DAFFODILDB.COM WWW.DAFFODILDB.COM

ReferenceReference

Any Question?Any Question?

sql server 2000 research series - transact sql

Technology