1 / 34

T-SQL

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

cmoreno
Download Presentation

T-SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. T-SQL Transact-SQL is microsoftimplementation of SQL. Itcontainsadditionalprogrammingconstracts T-SQL enablesyoutowriteprogramsthatcontain SQL statements.

  2. Using Variables You can declarevariableusing DECLARE statement, followedbythevariable name andthetype DECLARE @name type Forexample DECLARE @MyProductNamenvarchar(40) DECLARE @MyProductIdint

  3. Using Variables Variablesareinitially set tonull. You set a variables’ valueusing SET statement SET @MyProductName= ‘Chai’ SET @MyProductID = 7 Thefollowing SELECT statementusesthesevariables: Select ProductId, ProductName, UnitPrice FromProducts WhereProductId=@MyProductID OR ProductName=@MyProductName

  4. Using ConditionalLogic T-SQL enablesyoutouseconditionallogicoperatersin SQL stataments. IF-then-ELSE IF conditon statement 1 ELSE statement 2 You can replace a singlestatementwithmultiplestatementsbyplacingthosestatementswithin BEGIN and END statements

  5. Using ConditionalLogic If (Select Count(*) fromproductswhereunitprice<5)>0 BEGIN Print ‘Thefollowingproductshave a UnitPrice of lessthan 5’ Select ProductId, ProductName, UnitPrice FromProducts WhereUnitPrice<5 END Else BEGIN Print ‘Therearenoproductsthathave a UnitPrice of lessthan 5’ END

  6. Using CASE Statement Thefollowingexampleuses a selectstatementtoretrievethevalue Massachusetts returnedbythecasestatement: Declare @Statenchar(2) Set @State =‘MA’ Declare @StateNamenvarchar(15) Select Case @State as State When ‘CA’ then ‘California’ When ‘MA’ then ‘Massachusetts’ When ‘NY’ then ‘New York’ End

  7. Using CASE Statement You can storethevalueretrivedbythe SELECT statement in a variable as shown in theexample Declare @Statenchar(2) Set @State =‘MA’ Declare @StateNamenvarchar(15) Select @StateName= Case @State When ‘CA’ then ‘California’ When ‘MA’ then ‘Massachusetts’ When ‘NY’ then ‘New York’ End Print @StateName

  8. Using CASE Statement You can alsocompare a columnvalue 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

  9. WhileLoops Whileconditon statement Thefollowingexampleshows a whileloop: Declare @countint Set @count = 5 While (@count>0) Begin Print ‘count=’ + convert(nvarchar,@count) Set @count=@count-1; End

  10. Continue Statement You can usetheContinuestatementto start a nextiteration of whileloopimmediately, skippingoveranyremainingcode in theloop. Declare @countint 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

  11. Break Statement Declare @countint Set @count = 5 While (@count>0) Begin Print ‘count=’ + convert(nvarchar,@count) Set @count=@count-1; If (@count=2) Begin Break End End

  12. Using LabelsandtheGoto Statement YouusetheGotostatementtojump a specifiedlabel in yourcode; youuse a labeltoidentify a statement of yourcode. Declare @countint Set @count = 5 myLabel: Print ‘count=’ + convert(nvarchar,@count) Set @count=@count-1; If (@count>0) Begin GOTO myLabel End Output of thesecode is thesamewiththewhileloop’soutput

  13. Using Waitfor Statement Therearetimeswhenyouwantyour program topausebeforerunningsomecodetoperform a specificaction, such as running a batch program at nighttoupdatecustomerrecords WAITFOR {DELAY ‘ time interval’ | TIME ‘actual time’} WaitforDelay ‘00:00:05’ waitsfor a time interval of 5 seconds

  14. 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

  15. 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

  16. Using Raiserror Statement YouusetheRaiserrorstatementtogenerate an errormessage. It is typicallyusedtoif an error is occurs in one of yourstoredprocedures. Syntax: RAISERROR({number | description}{, severity, state}) Number is errornumberandbetween 50,001 and 2,147,483,648 Description is theerrormessage Severity is thedegree of theerrorandbetween 0 and 18

  17. 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;

  18. Using Cursor Whenyouexecute a SELECT statement, alltheeffectedrowsarereturned in onego. Sometimesyoumightwanttotakesomeactionbased on thecolumnvaluesretrivedfor a particularrow. To do this, you can use a cursortoprocessrowsretrivedfromthedatabaseonerow at a time.

  19. Using Cursor Youfollowthesestepswhenusing a cursor: DeclareVariablestoStoretheColumnValuesfromthe Select Statement Thesevariablesmust be compatiblewiththecolumntypesfortheretrievedrows. Example: DECLARE @MyProductIDint DECLARE @MyProductNamenvarchar (40) DECLARE @MyUnitPricemoney

  20. Using Cursor DeclaretheCursor A cursordeclarationconsists of a name thatyouassigntothecursorand a SELECT statementthatyouwanttoexecuteretrievedrows. This SELECT statement is not actuallyrununtilyouopenthecursor. Example: DECLARE ProductCursor CURSOR for Select ProductID, ProductName, UnitPrice fromProducts WhereProductID<=10

  21. Using Cursor Open theCursor Youopen a cursorusingtheOPENstatement. OPEN ProductCursor FetchtheRowsfromtheCursor Toreadeachrowfromyourcursor, usethefetchstatement Since theremight be manyrows, youneedtouseeWhileloop. Tocheckend of loop, use@@FETCH_STATUS function. Thisfunctionreturns: 0 if FETCH statementsuccessfullyreturned a row. -1 if FETCH statementfailedortherequestedrow is outsidetheresult set. -2 ifRowfetched is missing

  22. Using Cursor Thefollowingexampleshows a loopthatreadseachrowfromProductCursor. FetchNextfromProductCursor Into @MyProductID, @MyProductName, @MyUnitPrice Print ‘@MyProductID=‘ + Convert (nvarchar, @MyProductID) Print‘@MyProductName=‘ + Convert (nvarchar, @MyProductName) Print‘@MyUnitPrice=‘ + Convert (nvarchar, @MyUnitPrice) While @@Fetch_Status=0 Begin FetchNextfromProductCursor Into@MyProductID, @MyProductName, @MyUnitPrice Print‘@MyProductID=‘ + Convert (nvarchar, @MyProductID) Print‘@MyProductName=‘ + Convert (nvarchar, @MyProductName) Print‘@MyUnitPrice=‘ + Convert (nvarchar, @MyUnitPrice) END

  23. Using Cursor Close theCursor Youclose a cursorusingtheCLOSEstatement. CLOSEProductCursor Youshouldalsoremocethereferencetoyourcurs orusingtheDEALLOCATEstatement. DEALLOCATE ProductCursor

  24. Functions Therearemanybuilt-in functionstouse in T-SQL. You can finddetailedexplanationaboutthesefunctionsfromthe web site below: http://msdn.microsoft.com/en-us/library/ms174318.aspx

  25. Creating User-DefinedFunctions Youcreate a functionusingtheCREATE FUNCTIONstatement. Therearethreetypes of user-definedfunctions: ScalarFunctions: Thesefunctionsreturns a singlevalue. InlineTable-valuedFunctions: Returns an object of thetabletype. You can think of a table a aregulardatabasetable, except it is stored in memory. An inline table-valuedfunction can returntheresultsretrivedbyonly a single SELECT statement. Multistatementtable-valuedFunction: Returns a object of tabletypeand it can containmultiple T-SQL statement.

  26. Creating User-DefinedFunctions ScalarFunctions: TheexamplebelowcreatestheDiscountPrice() function, whichreturnstheoriginalprice of an itemmultipledby a discountfactor. CreateFunctionDiscountPrice(@OriginalPricemoney, @Discountmoney) Returns Money As Begin Return @OriginalPrice * @Discount End Using thisfunction: Declare @MyDiscountFactorFloat Set @MyDiscountFactor=0.3 Select dbo.DiscountPrice(UnitPrice,@MyDiscountFactor), UnitPrice FromProducts WhereProductID=1

  27. Creating User-DefinedFunctions InlineTable-ValuedFunctions: 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'

  28. Creating User-DefinedFunctions InlineTable-ValuedFunctions: 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.

  29. Creating User-DefinedFunctions CREATE FUNCTION fn_CustomerNamesInRegion ( @RegionParameternvarchar(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')

  30. Creating User-DefinedFunctions 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

  31. Creating User-DefinedFunctions InlineTable-ValuedFunctions: TheexamplebelowcreatestheProductsToBeReordered() function, whichreturns a tablecontainingtherowsfromtheProductstable CreateFunctionProductsToBeReordered(@ReorderLevelint) ReturnsTable As Return ( Select * FromProducts WhereUnitsInStock<=@ReorderLevel ) Using thisfunction: Select ProductID, ProductName, UnitsInStock FromProductsToBeReordered(10) WhereProductId<=50;

  32. Creating User-DefinedFunctions MultistatementTable-ValuedFunctions: 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.

  33. Creating User-DefinedFunctions This example creates a function in the Northwind database that returns a table: CREATE FUNCTION LargeOrderShippers ( @FreightParmmoney ) RETURNS @OrderShipperTab TABLE ( ShipperIDint, ShipperNamenvarchar(80), OrderIDint, ShippedDatedatetime, Freightmoney ) 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 )

  34. Creating User-DefinedFunctions MultistatementTable-ValuedFunctions: Theexamplebelowcreatesthe ProductsToBeReordered2() function, whichreturns a tablecontainingtherowsfromtheProductstable CreateFunctionProductsToBeReordered2 (@ReorderLevelint) Returns @MyProductstable ( ProductIDint, ProductNamenvarchar(40), UnitsInStocksmallint, Reordernvarchar(3) ) As Begin Insertinto @MyProducts Select ProductID, ProductName, UnitsInStock, ‘no’ fromProducts Update @MyProducts Set Reorder=‘yes’ WhereUnitsInStock<=@ReorderLevel Return End Usage of thefunction Select * from ProductsToBeReordered2(20)

More Related