TSQL TransactSQL is microsoft implementation of SQL It

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 @My. Product. Name nvarchar(40) DECLARE @My. Product. Id int

Using Variables • Variables are initially set to null. • You set a variables’ value using SET statement SET @My. Product. Name= ‘Chai’ SET @My. Product. ID = 7 The following SELECT statement uses these variables: Select Product. Id, Product. Name, Unit. Price From Products Where Product. Id=@My. Product. ID OR Product. Name=@My. Product. Name

Using Conditional Logic • T-SQL enables you to use conditional logic operaters in SQL stataments. • IF-then-ELSE IF 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)>0 BEGIN Print ‘The following products have a Unit. Price of less than 5’ Select Product. Id, Product. Name, Unit. Price From Products Where Unit. Price<5 END Else BEGIN Print ‘There are no products that have a Unit. Price 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 @State. Name 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 @State. Name nvarchar(15) Select @State. Name= Case @State When ‘CA’ then ‘California’ When ‘MA’ then ‘Massachusetts’ When ‘NY’ then ‘New York’ End Print @State. Name

Using CASE Statement You can also compare a column value in a CASE statement Select Price= Case When Unit. Price is NULL then 'Unknown' When Unit. Price <10 then 'Less than 10' When Unit. Price =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) Begin Print ‘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) Begin Print ‘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) Begin Print ‘count=’ + convert(nvarchar, @count) Set @count=@count-1; If (@count=2) Begin Break 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 my. Label: Print ‘count=’ + convert(nvarchar, @count) Set @count=@count-1; If (@count>0) Begin GOTO my. Label 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: 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 = 'Test. Job'; BEGIN WAITFOR TIME '22: 20'; EXECUTE sp_update_job @job_name = 'Test. Job', @new_name = 'Updated. Job'; 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 @Error. Message NVARCHAR(4000); DECLARE @Error. Severity INT; DECLARE @Error. State INT; SELECT @Error. Message = ERROR_MESSAGE(), @Error. Severity = ERROR_SEVERITY(), @Error. State = 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 (@Error. Message, -- Message text. @Error. Severity, -- Severity. @Error. State -- 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 @My. Product. ID int DECLARE @My. Product. Name nvarchar (40) DECLARE @My. Unit. Price 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 Product. Cursor CURSOR for Select Product. ID, Product. Name, Unit. Price from Products Where Product. ID<=10

Using Cursor • • Open the Cursor You open a cursor using the OPEN statement. OPEN Product. Cursor 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 Cursor The following example shows a loop that reads each row from Product. Cursor. Fetch Next from Product. Cursor Into @My. Product. ID, @My. Product. Name, @My. Unit. Price Print ‘@My. Product. ID=‘ + Convert (nvarchar, @My. Product. ID) Print ‘@My. Product. Name=‘ + Convert (nvarchar, @My. Product. Name) Print ‘@My. Unit. Price=‘ + Convert (nvarchar, @My. Unit. Price) While @@Fetch_Status=0 Begin Fetch Next from Product. Cursor Into @My. Product. ID, @My. Product. Name, @My. Unit. Price Print ‘@My. Product. ID=‘ + Convert (nvarchar, @My. Product. ID) Print ‘@My. Product. Name=‘ + Convert (nvarchar, @My. Product. Name) Print ‘@My. Unit. Price=‘ + Convert (nvarchar, @My. Unit. Price) END

Using Cursor • Close the Cursor You close a cursor using the CLOSE statement. CLOSE Product. Cursor You should also remoce the reference to your curs or using the DEALLOCATE statement. DEALLOCATE Product. Cursor

Functions • There are many built-in functions to use in TSQL. • You can find detailed explanation about these functions from the web site below: http: //msdn. microsoft. com/enus/library/ms 174318. 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 TSQL statement.

Creating User-Defined Functions Scalar Functions: The example below creates the Discount. Price() function, which returns the original price of an item multipled by a discount factor. Create Function Discount. Price(@Original. Price money, @Discount money) Returns Money As Begin Return @Original. Price * @Discount End Using this function: Declare @My. Discount. Factor Float Set @My. Discount. Factor =0. 3 Select dbo. Discount. Price(Unit. Price, @My. Discount. Factor ), Unit. Price From Products Where Product. ID=1

Creating User-Defined Functions Inline Table-Valued Functions: Inline userdefined 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_Customer. Names. In. WA AS SELECT Customer. ID, Company. Name FROM Northwind. dbo. Customers WHERE Region = 'WA'

Creating User-Defined Functions Inline Table-Valued Functions: You can create a more generalized version, vw_Customer. Names. In. Region, by replacing the WHERE Region = 'WA' with a WHERE Region = @Region. Parameter 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 Functions CREATE FUNCTION fn_Customer. Names. In. Region ( @Region. Parameter nvarchar(30) ) RETURNS table AS RETURN ( SELECT Customer. ID, Company. Name FROM Northwind. dbo. Customers WHERE Region = @Region. Parameter ) -- Example of calling the function for a specific region SELECT * FROM fn_Customer. Names. In. Region(N'WA')

Creating User-Defined Functions Inline 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 Functions Inline Table-Valued Functions: The example below creates the Products. To. Be. Reordered() function, which returns a table containing the rows from the Products table Create Function Products. To. Be. Reordered (@Reorder. Level int) Returns Table As Return ( Select * From Products Where Units. In. Stock<=@Reorder. Level ) Using this function: Select Product. ID, Product. Name, Units. In. Stock From Products. To. Be. Reordered(10) Where Product. Id<=50;

Creating User-Defined Functions Multistatement 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 Functions This example creates a function in the Northwind database that returns a table: CREATE FUNCTION Large. Order. Shippers ( @Freight. Parm money ) RETURNS @Order. Shipper. Tab TABLE ( Shipper. ID int, Shipper. Name nvarchar(80), Order. ID int, Shipped. Date datetime, Freight money ) AS This query references the table BEGIN returned by the function in its INSERT @Order. Shipper. Tab FROM clause: SELECT S. Shipper. ID, S. Company. Name, SELECT * FROM Large. Order. Shippers( $500 ) O. Order. ID, O. Shipped. Date, O. Freight FROM Shippers AS S INNER JOIN Orders AS O ON S. Shipper. ID = O. Ship. Via WHERE O. Freight > @Freight. Parm RETURN END

Creating User-Defined Functions Multistatement Table-Valued Functions: The example below creates the Products. To. Be. Reordered 2() function, which returns a table containing the rows from the Products table Create Function Products. To. Be. Reordered 2 (@Reorder. Level int) Returns @My. Products table ( Product. ID int, Product. Name nvarchar(40), Units. In. Stock smallint, Reorder nvarchar(3) ) As Begin Insert into @My. Products Select Product. ID, Product. Name, Units. In. Stock, ‘no’ from Products Update @My. Products Set Reorder=‘yes’ Where Units. In. Stock<=@Reorder. Level Return End Usage of the function Select * from Products. To. Be. Reordered 2(20)