WIXLIB

Microsoft SQL Server.T-SQL IDENTIFIERSRegular identifiersSalary @Salary Salary123 Boss SalaryDelimited identifiersAre enclosed in double quotation marks (") or brackets ([ ]).[Big Salary]“StrangeName” [First Name]Examplesdeclare @var1 intdeclare @var2 numeric(10,2), @var3 nvarchar(20)28

Microsoft SQL Server.Data ngsUnicodeCharacterStringsBinaryStringsOtherData harbinaryimagevarbinarycursortimestampsql me29

Microsoft SQL Server.SQL-Server FunctionsFunction categoryDescriptionAggregate Functions (Transact-SQL)Summary operations. Examples are COUNT, SUM, MIN, and MAX.Configuration functionsScalar functions that return information about configuration settings.Cryptographic Functions (Transact-SQL)Support encryption, decryption, digital signing, and the validation of digital signatures.Cursor functionsReturn information about the status of a cursor.Date and Time functionsChange date and time values.Mathematical functionsPerform trigonometric, geometric, and other numeric operations.Metadata functionsReturn information about the attributes of databases and database objects.Ranking functionsNondeterministic functions that return a ranking value for each row in a partition.Rowset Functions (Transact-SQL)Return the rowsets that can be used in the place of a table reference in a Transact-SQLstatement.Security functionsReturn information about users and roles.String functionsChange char, varchar, nchar, nvarchar, binary, and varbinary values.System functionsOperate on or report on various system level options and objects.System Statistical Functions (Transact-SQL)Return information about the performance of SQL Server.Text and image functionsChange text and image values.30

Microsoft SQL Server.SQL-Server INCHECKSUMSUMCHECKSUM AGGSTDEVCOUNTSTDEVPCOUNT @@LOCK TIMEOUT@@SPID@@MAX CONNECTIONS@@TEXTSIZE@@MAX PRECISION@@VERSION@@NESTLEVEL@@CURSOR ROWS@@FETCH STATUSCURSOR TRTRIMSOUNDEXSPACESTRSTUFFSUBSTRINGUNICODEUPPER31

Microsoft SQL Server.SQL-Server FunctionsDate ROUNDSIGNSINSQRTSQUARETANMetaData@@PROCIDCOL LENGTHCOL EXDB IDDB NAMEFILE IDFILE IDEX (Transact-SQL)FILE NAMEFILEGROUP IDFILEGROUP OPERTYFULLTEXTSERVICEPROPERTYINDEX COLINDEXKEY PROPERTYINDEXPROPERTYOBJECT IDOBJECT NAMEOBJECTPROPERTYOBJECTPROPERTYEXSQL VARIANT PROPERTYTYPE IDTYPE NAMETYPEPROPERTY32

Microsoft SQL Server.Procedural T-SQLUsing batchesA batch is a group of one or more Transact-SQL statements that are sent froman application to the server as one unit.Using stored proceduresA stored procedure is a group of Transact-SQL statements that have beenpredefined and precompiled on the server. They can accept parameters, andreturn result sets, return codes, and output parameters to the caller.Using triggersA trigger is a special type of stored procedure. It is not called directly byapplications. It is instead executed whenever a user performs a specifiedmodification (INSERT, UPDATE, or DELETE) to a table.Using scriptsA script is a series of Transact-SQL statements stored in a file. The file can beused as input to the sqlcmd utility or SQL Server Management Studio Code33editor.

Microsoft SQL Server.Flow-Control, Iteration ORGOTOWHILE34

Microsoft SQL Server.Var Declaration – Assignment Statement-- assignment operator (SELECT or SET)declare @var1 intdeclare @var2 numeric(10,2), @var3 nvarchar(20)set @var1 123select @var1 123 -- same as previous assignmentset @var2 19.99set @var3 'Juan Valdez'set @var2 CAST(@var1 as numeric(10,2) )set @var2 CONVERT(numeric, @var1)set @var3 'Room No. ' Str(@var1)35

Microsoft SQL Server.Flow-Control StatementsBEGINstatement-1statement-2 statement-nENDIF @Grade 75print ‘Pass’ELSEprint ‘Fail’36

Microsoft SQL Server.Flow-Control Statementsdeclare @n intset @n 5declare @n intWHILE @n 0BEGINprint 'n ' str(@n)set @n @n - 1ENDset @n @@rowCountselect * from employeeIF @n 0print 'No data found'ELSEprint 'Total rows: ' str(@n)37

Microsoft SQL Server.WAITFOR Flow-Control StatementsThe following example uses the DELAY keyword to wait for two seconds beforeperforming a SELECT statement:WAITFOR DELAY '00:00:02'SELECT * FROM Employee;The following example uses the TIME keyword to wait until 10 P.M. to perform acheck of the Company database to make sure that all Projects are in progressUSE Company;GOBEGINWAITFOR TIME '22:00:00';exec CloseTheGatesEND;GO38

Microsoft SQL Server.Binding Local Variables with SQL statementsBEGINdeclare @idintdeclare @sal moneydeclare @name nvarchar(20)select @id ssn, @sal salary, @name fnamefrom employeewhere lname like 'Smith%'print str(@id) ' ' str(@sal) ' ' @nameENDGo39

Microsoft SQL Server.Binding Local Variables with SQL statementsdeclare @OldSalary moneyupdate employeeset @OldSalary salary,salary salary 1where ssn 123456789print 'Old salary: ' str(@OldSalary)40

Microsoft SQL Server.Using the global @@ERROR variableBEGINupdate employeeset salary salary / 0where ssn 123456789if @@error 0print 'Some bizarre problem ' 'happened here! - ' 'call Xagents Mulder & Scully'ENDMsg 8134, Level 16, State 1, Line 2Divide by zero error encountered.The statement has been terminated.Some bizarre problem happend here! - call Mulder and Scully41

Microsoft SQL Server.Using the global @@ROWCOUNT variableDelete employees working for dno 4 (then restore the table)-- create temporary copy of the EMPLOYEE tableselect * into #EmpCopy from employeegodeclare @myCount intBEGINbegin transactiondelete from #EmpCopy where dno 4-- @@rowCount tells how many rows were affected in previous stm.set @myCount @@rowCountif @myCount 0 beginselect * from #EmpCopyprint 'Deleted rec. ' str(@myCount)rollbackprint 'employee table restored'endelse beginprint 'NO rec. deleted ' str(@myCount)commitendENDselect * from #EmpCopy42

Microsoft SQL Server.Using the global @@ROWCOUNT variableTransaction ProcessingBegin Transactionstatementstatement statementCommit (or Rollback)43

Microsoft SQL Server.Using the RAND() Functiondelete from mylogdeclare @msg varchar(50), @num int, @n intset @n 5while @n 0begin--make a random numb between 1.100set @num RAND() * 100 1set @msg 'Random message number ' str(@num)insert into mylog values (@n, @msg)set @n @n - 1endselect * from mylog44

Microsoft SQL Server.T-SQL CursorsT-SQL Cursors45

Microsoft SQL Server.T-SQL CursorsA SQL select-statement retrieves a complete set of rows. Someapplications may need a mechanism to work with one row or asmall block of rows at a time.Cursors are an extension to result sets that provide that mechanism by Allowing positioning at specific rows of the result set. Retrieving one row or block of rows from the current position in the resultset. Supporting data modifications to the rows at the current position in the resultset. Supporting different levels of visibility to changes made by other users to thedatabase data that is presented in the result set. Providing Transact-SQL statements in scripts, stored procedures, andtriggers access to the data in a result set.46

Microsoft SQL Server.T-SQL CursorsSyntax-- drop an existing cursordeallocate curPeople-- create a cursordeclare curPeople cursor forselect fname, salaryfrom employeewhere dno 4-- open / close cursoropen curPeopleclose curPeople-- fetching rows / checking statefetch from curPeople into @var1, @var2, .@@fetch status ( 0 when data read )47

Microsoft SQL Server.T-SQL CursorsEXAMPLE-- get first & last name of female employeesdeallocate curPeopledeclare curPeople cursor forselect fname, lnamefrom employeewhere sex 'F'--define local variables to store from cursordeclare @fn nvarchar(20), @ln varchar(20)open curPeoplefetch from curPeople into @fn, @lnwhile(@@fetch status 0)beginprint @fn ' ' @lnfetch from curPeople into @fn, @lnendclose curPeople48

Microsoft SQL Server.T-SQL Forward-Only Cursorsdeallocate curPeopleGodeclare curPeople cursor forselect fname, salaryfrom employeeGoBEGIN TRANSACTIONdeclare @eName nvarchar(100), @eSal numeric(10,2)open curPeoplefetch from curPeople into @eName, @eSalwhile @@fetch status 0 beginif @eSal 40000 beginupdate employee set salary salary 1where current of curPeopleprint 'New salary ' @eName str(@eSal)endfetch from curPeople into @eName, @eSalendclose curPeopleROLLBACK TRANSACTIONGo49

Microsoft SQL Server.T-SQL Scrollable - Cursors--scrollable cursorsdeallocate scrPeopledeclare scrPeople scroll cursor forselect (fname N' ' lname) as fullName, salary from employee order by salarydeclare @eName nvarchar(100), @eSal numeric(10,2)open scrPeoplefetch NEXT from scrPeople into @eName, @eSalprint str(@@fetch status) ' ' @eName char(9) str(@eSal)fetch LAST from scrPeople into @eName, @eSalprint str(@@fetch status) ' ' @eName char(9) str(@eSal)fetch PRIOR from scrPeople into @eName, @eSalprint str(@@fetch status) ' ' @eName char(9) str(@eSal)-- Fetch the second row in the cursorfetch ABSOLUTE 2 from scrPeople into @eName, @eSalprint str(@@fetch status) ' ' @eName char(9) str(@eSal)-- Fetch the row that is 10 rows after the current rowfetch RELATIVE 10 from scrPeople into @eName, @eSalprint str(@@fetch status) ' ' @eName char(9) str(@eSal)0 Joyce English0 James Borg0 Jennifer Wallace0 Ahmad Jabbar-1 Ahmad Jabbar302516063747407302513025150close scrPeople

Microsoft SQL Server.T-SQL Stored ProceduresT-SQLStored Procedures51

Microsoft SQL Server.T-SQL Stored Procedures with OUTPUT valuesIF EXISTS (select * from SYSOBJECTS where name 'GetDepartment')DROP PROCEDURE GetDepartmentGOcreate procedure GetDepartment (@ID int, @depName nvarchar(20)asBEGINselect @depName d.dname from employee e, department dwhere e.dno d.dnumber and e.ssn @IDif @@rowCount 1return 0 -- department foundelse beginset @depName ''return 1 -- department NOT foundendENDGOdeclare @answer nvarchar(20)Set @answer '***'exec GetDepartment 123456789, @answer outputprint 'The Department for 123456789 is: ' @answeroutput)52

Microsoft SQL Server.T-SQL Stored Procedures with OUTPUT valuesdeclare @answer nvarchar(20)set @answer ''exec GetDepartment 123456789, @answer outputprint 'The Department for 123456789 is: ' @answerGOThe Department for 123456789 is: Researchdeclare @myRetCode intdeclare @answer nvarchar(20)set @answer '***'set @myRetCode -1Execute @myRetCode GetDepartment 123456789, @answer OUTPUTprint 'The ReturnCode is' Str(@myRetCode)print 'The Department for 123456789 is: ' @answerGOThe ReturnCode is 0The Department for 123456789 is: Research53

Microsoft SQL Server.Using a .NET program to reach aT-SQL Stored Procedures with OUTPUT valuesPrivate Function UseSsnToGetDeptName(ByVal aSSN As Integer) As StringDim myCnn As New SqlConnectionDim myCmd As New SqlCommandmyCnn.ConnectionString "Data Source Localhost;Initial Catalog Company;Integrated Security True"myCnn.Open()myCmd.CommandType CommandType.StoredProceduremyCmd.Connection myCnnmyCmd.CommandText eters.Add("@empSSN", SqlDbType.Int)myCmd.Parameters.Add("@depName", SqlDbType.VarChar, 100)myCmd.Parameters("@depName").Direction ").Value aSSNmyCmd.Parameters("@depName").Value "***"myCmd.ExecuteNonQuery()Return nd Function54

Microsoft SQL Server.T-SQL Stored Procedures with OUTPUT values-- STORED PROCEDURES: returned values (OUTPUT vars), DEFAULT valuesdrop procedure pr GetSalaryGocreate proc pr GetSalary(@id int,@sal numeric(20,2) output,@currency nvarchar(10) 'usd' )asdeclare @ctr int, @xrate numeric(10,2)BEGINset @sal 0select @sal salary from employee where ssn @idif @@rowCount 0 return -1if @currency 'usd'set @xrate 1.00if @currency 'euros' set @xrate 0.73if @currency 'bolivares' set @xrate 2700.00select @sal salary from employee where ssn @idset @sal @sal * @xratereturn 0END55

Microsoft SQL Server.T-SQL Stored Procedures with OUTPUT values--Testing OUTPUT proc and RETURN valuesdeclare @result numeric(20,2), @retCode intset @result 0, ret--no currency supplied (use default 'usd' )exec pr GetSalary 987654321, @result outputprint 'Salary (usd) ' str(@result)Salary (usd) 47407Salary (euros) 36977Salary (bolivares) 118517500Return code is:-1 Salary (usd) 0Return code is:0 Salary (usd) 36314exec pr GetSalary 987654321, @result output, 'euros'print 'Salary (euros) ' str(@result)exec pr GetSalary 987654321, @result output, 'bolivares'print 'Salary (bolivares) ' str(@result)-- using RETURN CODES. No currency supplied (use default 'usd' )exec @retCode pr GetSalary 123, @result outputprint 'Return code is: ' str(@retCode) ' Salary (usd) ' str(@result)exec @retCode pr GetSalary 123456789, @result outputprint 'Return code is: ' str(@retCode) ' Salary (usd) ' str(@result)go56

Microsoft SQL Server.T-SQL Stored Functionsdrop function dbo.fnGetNamegoCREATE FUNCTION dbo.fnGetName (@aSSN int)RETURNS nvarchar(80)AS BEGIN-- Returns salutation full-name of given employeeDECLARE @answer nvarchar(100), @gender nvarchar(2)set @answer ''select @gender sex from employee where ssn @aSSN;if @@rowcount 0 RETURN @answer;select @answer (fname ' ' lname) from employeewhere ssn @aSSN;if @gender 'F'set @answer 'Ms. ' @answerelseset @answer 'Mr. ' @answer;RETURN @answerEND;GOdeclare @person nvarchar(100)set @person dbo.fnGetName (123456789)print @personselect ssn, dbo.fnGetName(ssn) from employee57

Microsoft SQL Server.CLR Stored FunctionsImports Microsoft.SqlServer.ServerImports System.Data.SqlClientPublic Class T SqlFunction(DataAccess: DataAccessKind.Read) Public Shared Function GetFullName(ByVal aSSN As Integer) As IntegerUsing conn As New SqlConnection("context connection true")conn.Open()Dim mySQL As String "SELECT (Fname ' ' Lname) as FullName, sex " &" FROM Employee WHERE ssn " & aSSNDim cmd As New SqlCommand(mySQL, conn)Dim DR As SqlDataReaderDim answer As String "* * *"DR cmd.ExecuteReader()If DR.Read ThenIf DR("sex") "F" Thenanswer "Ms. " DR("fullName")Elseanswer "Mr. " DR("FullName")End IfEnd IfReturn answerEnd UsingEnd FunctionEnd Class58

Microsoft SQL Server.CLR Stored Functionsdrop assembly myFunctiongoCREATE ASSEMBLY myFunction FROM 'c:\temp\SQLCLRfunction.dll'goCREATE FUNCTION GetName() RETURNS nvarcharAS EXTERNAL NAME SQLCLRfunction.T.GetNamegodeclare @X nvarchar(100)SET @x dbo.GetName()go59

Microsoft SQL Server.T-SQL Exceptions & RAISERROR commandError Handling60

Microsoft SQL Server.T-SQL Exceptions & RAISERROR commandThe command: raiserror behaves like C printf( )raiserror(text-message, severity, state, ate(0-127),arguments (used in combination with % formatters)Example:raiserror('Error. negative argument %d',17, 55, @arg1)61

Microsoft SQL Server.T-SQL Exceptions & RAISERROR command-- EXCEPTIONS. Using RAISERROR commanddrop procedure TestErrorGocreate procedure TestError (@arg1 int)asbeginif @arg1 0 begin--raisError behaves like C printf( )--severity(0-18), state(0-127), argumentsraiserror('Error. negative argument %d',17, 55, @arg1)endprint 'Busy doing something here with ' str(@arg1)print 'Adios from your friendly procedure'endGoWarning: Exception is RAISED but execution continues !exec TestError -11Busy doing something here with-11Adios from your friendly procedureMsg 50000, Level 17, State 55, Procedure TestError, Line 6Error. negative argument -1162

Microsoft SQL Server.T-SQL Exceptions & RAISERROR command-- EXCEPTIONS. Using RAISERROR commanddrop procedure pr SpouseGocreate procedure pr Spouse (@id int, @spouseName nvarchar(20) output)asBEGINset @spouseName '***'select * from employee where ssn @idif @@rowCount 0 begin--message like C printf, severity(0-18), state(0-127), argumentsraiserror('Error1. Invalid employee SSN %d',17, 55, @id)-- raiserror('Error2. Invalid employee SSN %d',17, 55, @id)-- raiserror('Error3. Invalid employee SSN %d',17, 55, @id)-- return 1endselect @spouseName dependent namefrom dependentwhere relationship 'Spouse' and essn @idif @@rowCount 0return 2elsereturn 0END(0 row(s) affected)Spouse: *** Return Code: 1Msg 50000, Level 17, State 55, Procedure pr Spouse, Line 9Error. Invalid employee SSN 123godeclare @mate nvarchar(40), @retCode intexecute @retCode pr Spouse 123, @mate outputprint 'Spouse: ' @mate ' - Return Code: ' ltrim(str(@retCode))63

Microsoft SQL Server.T-SQL Exceptions & RAISERROR commandImports System.Data.SqlClientPublic Class Form1Private Sub btnFind Click(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles btnFind.ClickTryDim myCnn As New SqlConnectionDim myCmd As New SqlCommandmyCnn.ConnectionString "Data Source Localhost;Initial Catalog CompanySQL;Integrated Security True"myCnn.Open()myCmd.CommandType CommandType.Stored

Microsoft-SQL Server 2005 is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment. SQL Server runs on T-SQL ( Transact -SQL ), a set of programming extensions from Sybase and Microsoft that enhances the capabilities of standard SQL. According to Microso