T-SQL

• Transact-SQL is microsoft implementation of SQL.

• It contains additional programming constracts

• T-SQL enables you to write programs that contain SQL statements.

Using Variables

• You can declare variable using DECLARE statement, followed by the variable name and the type

DECLARE @name type

For example

DECLARE @MyProductName nvarchar(40)

DECLARE @MyProductId int

Using Variables

• Variables are initially set to null.• You set a variables’ value using SET statement

SET @MyProductName= ‘Chai’

SET @MyProductID = 7

The following SELECT statement uses these variables:

Select ProductId, ProductName, UnitPrice

From Products

Where ProductId=@MyProductID OR

ProductName=@MyProductName

Using Conditional Logic

• T-SQL enables you to use conditional logic operaters in SQL stataments.

• IF-then-ELSEIF conditon

statement 1

ELSE

statement 2

You can replace a single statement with multiple statements by placing those statements within BEGIN and END statements

Using Conditional Logic

If (Select Count(*) from products where unitprice<5)>0BEGIN

Print ‘The following products have a UnitPrice of less than 5’

Select ProductId, ProductName, UnitPrice

From Products

Where UnitPrice<5

END

ElseBEGIN

Print ‘There are no products that have a UnitPrice of less than 5’

END

Using CASE Statement

The following example uses a select statement to retrieve the value Massachusetts returned by the case statement:

Declare @State nchar(2)

Set @State =‘MA’

Declare @StateName nvarchar(15)

Select Case @State as State

When ‘CA’ then ‘California’

When ‘MA’ then ‘Massachusetts’

When ‘NY’ then ‘New York’

End

Using CASE Statement

You can store the value retrived by the SELECT statement in a variable as shown in the example

Declare @State nchar(2)

Set @State =‘MA’

Declare @StateName nvarchar(15)

Select @StateName=

Case @State

When ‘CA’ then ‘California’

When ‘MA’ then ‘Massachusetts’

When ‘NY’ then ‘New York’

End

Print @StateName

Using CASE Statement

You can also compare a column value in a CASE statement

Select Price=

Case

When UnitPrice is NULL then 'Unknown'

When UnitPrice <10 then 'Less than 10'

When UnitPrice =10 then '10'

Else 'Greater than 10'

End

From Products

While Loops

While conditon

statement

The following example shows a while loop:

Declare @count int

Set @count = 5

While (@count>0)

BeginPrint ‘count=’ + convert(nvarchar,@count)

Set @count=@count-1;

End

Continue Statement

You can use the Continue statement to start a next iteration of while loop immediately, skipping over any remaining code in the loop.

Declare @count int

Set @count = 5

While (@count>0)

BeginPrint ‘count=’ + convert(nvarchar,@count)

Set @count=@count-1;

if (@count=2)

Begin

Set @count=@count-1;

Continue

end

End

Break Statement

Declare @count int

Set @count = 5

While (@count>0)

BeginPrint ‘count=’ + convert(nvarchar,@count)

Set @count=@count-1;

If (@count=2)

Begin

Break

End

End

Using Labels and the Goto Statement

You use the Goto statement to jump a specified label in your code; you use a label to identify a statement of your code.

Declare @count int

Set @count = 5

myLabel:

Print ‘count=’ + convert(nvarchar,@count)

Set @count=@count-1;

If (@count>0)

Begin

GOTO myLabel

End

Output of these code is the same with the while loop’s output

Using Waitfor Statement

There are times when you want your program to pause before running some code to perform a specific action, such as running a batch program at night to update customer records

WAITFOR {DELAY ‘ time interval’ | TIME ‘actual time’}

Waitfor Delay ‘00:00:05’ waits for a time interval of 5 seconds

Using Waitfor Statement

Using WAITFOR TIME: The following example executes the stored procedure sp_update_job at 10:20 P.M. (22:20).

USE msdb;

EXECUTE sp_add_job @job_name = 'TestJob';

BEGIN

WAITFOR TIME '22:20';

EXECUTE sp_update_job @job_name = 'TestJob',

@new_name = 'UpdatedJob';

END;

GO

Using Waitfor Statement

Using WAITFOR DELAY: The following example executes the stored procedure after a two-hour delay.

BEGIN

WAITFOR DELAY '02:00';

EXECUTE sp_helpdb;

END;

GO

Using Raiserror Statement

• You use the Raiserror statement to generate an error message.

• It is typically used to if an error is occurs in one of your stored procedures.

Syntax:RAISERROR({number | description}{, severity, state})• Number is error number and between 50,001 and

2,147,483,648• Description is the error message• Severity is the degree of the error and between 0 and 18

Using Raiserror Statement• The following code example shows how to use RAISERROR inside a TRY block to cause

execution to jump to the associated CATCH block. It also shows how to use RAISERROR to return information about the error that invoked the CATCH block.

BEGIN TRY

-- RAISERROR with severity 11-19 will cause execution to

-- jump to the CATCH block.

RAISERROR ('Error raised in TRY block.', -- Message text.

16, -- Severity.

1 -- State.

);

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();

-- Use RAISERROR inside the CATCH block to return error

-- information about the original error that caused

-- execution to jump to the CATCH block.

RAISERROR (@ErrorMessage, -- Message text.

@ErrorSeverity, -- Severity.

@ErrorState -- State.

);

END CATCH;

Using Cursor

• When you execute a SELECT statement, all the effected rows are returned in one go.

• Sometimes you might want to take some action based on the column values retrived for a particular row.

• To do this, you can use a cursor to process rows retrived from the database one row at a time.

Using Cursor

You follow these steps when using a cursor:• Declare Variables to Store the Column Values from the

Select Statement• These variables must be compatible with the column types

for the retrieved rows.• Example:

DECLARE @MyProductID int

DECLARE @MyProductName nvarchar (40)

DECLARE @MyUnitPrice money

Using Cursor

• Declare the Cursor• A cursor declaration consists of a name that you assign to the

cursor and a SELECT statement that you want to execute retrieved rows.

• This SELECT statement is not actually run until you open the cursor.

• Example:

DECLARE ProductCursor CURSOR for

Select ProductID, ProductName, UnitPrice

from Products

Where ProductID<=10

Using Cursor• Open the Cursor

You open a cursor using the OPEN statement.

OPEN ProductCursor• Fetch the Rows from the Cursor• To read each row from your cursor, use the fetch statement• Since there might be many rows, you need to usee While loop.• To check end of loop, use @@FETCH_STATUS function. This function

returns:• 0 if FETCH statement successfully returned a row.• -1 if FETCH statement failed or the requested row is outside the

result set.• -2 if Row fetched is missing

Using CursorThe following example shows a loop that reads each row from ProductCursor.

Fetch Next from ProductCursor

Into @MyProductID, @MyProductName, @MyUnitPrice

Print ‘@MyProductID=‘ + Convert (nvarchar, @MyProductID)

Print ‘@MyProductName=‘ + Convert (nvarchar, @MyProductName)

Print ‘@MyUnitPrice=‘ + Convert (nvarchar, @MyUnitPrice)

While @@Fetch_Status=0

Begin

Fetch Next from ProductCursor

Into @MyProductID, @MyProductName, @MyUnitPrice

Print ‘@MyProductID=‘ + Convert (nvarchar, @MyProductID)

Print ‘@MyProductName=‘ + Convert (nvarchar, @MyProductName)

Print ‘@MyUnitPrice=‘ + Convert (nvarchar, @MyUnitPrice)

END

Using Cursor

• Close the CursorYou close a cursor using the CLOSE statement.

CLOSE ProductCursor

You should also remoce the reference to your curs

or using the DEALLOCATE statement.

DEALLOCATE ProductCursor

Functions

• There are many built-in functions to use in T-SQL.

• You can find detailed explanation about these functions from the web site below:

http://msdn.microsoft.com/en-us/library/ms174318.aspx

Creating User-Defined Functions

• You create a function using the CREATE FUNCTION statement.

• There are three types of user-defined functions:• Scalar Functions: These functions returns a single value.• Inline Table-valued Functions: Returns an object of the

table type. You can think of a table a a regular database table, except it is stored in memory. An inline table-valued function can return the results retrived by only a single SELECT statement.

• Multistatement table-valued Function: Returns a object of table type and it can contain multiple T-SQL statement.

Creating User-Defined FunctionsScalar Functions: The example below creates the DiscountPrice() function, which returns the original price of an item multipled by a discount factor.

Create Function DiscountPrice(@OriginalPrice money, @Discount money)

Returns Money

As

Begin

Return @OriginalPrice * @Discount

EndUsing this function:

Declare @MyDiscountFactor Float

Set @MyDiscountFactor =0.3

Select dbo.DiscountPrice(UnitPrice,@MyDiscountFactor ), UnitPrice

From Products

Where ProductID=1

Creating User-Defined FunctionsInline Table-Valued Functions: Inline user-defined functions are a subset of user-defined functions that return a table.

Inline functions can be used to achieve the functionality of parameterized views.

Consider this view:

CREATE VIEW vw_CustomerNamesInWA AS

SELECT CustomerID, CompanyName

FROM Northwind.dbo.Customers

WHERE Region = 'WA'

Creating User-Defined FunctionsInline Table-Valued Functions: You can create a more generalized version, vw_CustomerNamesInRegion, by replacing the WHERE Region = 'WA' with a WHERE Region = @RegionParameter and letting users specify the region they are interested in viewing. Views, however, do not support parameters in the search conditions specified in the WHERE clause.

Creating User-Defined FunctionsCREATE FUNCTION fn_CustomerNamesInRegion

( @RegionParameter nvarchar(30) )

RETURNS table

AS

RETURN (

SELECT CustomerID, CompanyName

FROM Northwind.dbo.Customers

WHERE Region = @RegionParameter

)

-- Example of calling the function for a specific region

SELECT *

FROM fn_CustomerNamesInRegion(N'WA')

Creating User-Defined FunctionsInline user-defined functions follow these rules:• The RETURNS clause contains only the keyword table. You do not

have to define the format of a return variable because it is set by the format of the result set of the SELECT statement in the RETURN clause.

• There is no function_body delimited by BEGIN and END.

• The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

• The table-valued function accepts only constants or @local_variable arguments

Creating User-Defined FunctionsInline Table-Valued Functions: The example below creates the ProductsToBeReordered() function, which returns a table containing the rows from the Products table

Create Function ProductsToBeReordered (@ReorderLevel int)

Returns Table

As

Return

(Select *

From Products

Where UnitsInStock<=@ReorderLevel

)Using this function:

Select ProductID, ProductName, UnitsInStock

From ProductsToBeReordered(10)

Where ProductId<=50;

Creating User-Defined FunctionsMultistatement Table-Valued Functions: User-defined functions that return a table can be powerful alternatives to views. A user-defined function that returns a table can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

In a user-defined function that returns a table:• The RETURNS clause defines a local return variable name for the table

returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.

• The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.

• When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.

Creating User-Defined FunctionsThis example creates a function in the Northwind database that returns a table:CREATE FUNCTION LargeOrderShippers ( @FreightParm money )

RETURNS @OrderShipperTab TABLE

(

ShipperID int,

ShipperName nvarchar(80),

OrderID int,

ShippedDate datetime,

Freight money

)

AS

BEGIN

INSERT @OrderShipperTab

SELECT S.ShipperID, S.CompanyName,

O.OrderID, O.ShippedDate, O.Freight

FROM Shippers AS S INNER JOIN Orders AS O

ON S.ShipperID = O.ShipVia

WHERE O.Freight > @FreightParm

RETURN

END

This query references the table returned by the function in its FROM clause:SELECT *FROM LargeOrderShippers( $500 )

Creating User-Defined FunctionsMultistatement Table-Valued Functions: The example below creates the ProductsToBeReordered2() function, which returns a table containing the rows from the Products tableCreate Function ProductsToBeReordered2 (@ReorderLevel int)

Returns @MyProducts table

(

ProductID int, ProductName nvarchar(40), UnitsInStock smallint, Reorder nvarchar(3)

)

As

Begin

Insert into @MyProducts

Select ProductID, ProductName, UnitsInStock, ‘no’ from Products

Update @MyProducts

Set Reorder=‘yes’

Where UnitsInStock<=@ReorderLevel

Return

End

Usage of the function

Select * from ProductsToBeReordered2(20)