Programming in TransactSQL

1
Programming in Transact-SQL

• Erin Welker
• Consultant

2
What is Transact-SQL?

• When do I use it?
• Stored Procedures
• Triggers
• Adhoc queries
• Where do I use it?
• Query Analyzer
• OSQL
• Scripts DTS, SQL Agent, etc.

3
SELECT Statement -The Core of SQL

• SELECT select_listINTO new_table_FROM
  table_sourceWHERE search_conditionGROUP BY
  group_by_expressionHAVING search_conditionOR
  DER BY order_expression ASC DESC

4
CASE Statement

• Expression, not control-of-flow keyword
• Can only be used inside another statement
• SELECT title, price, classification
• CASE
• WHEN price lt 10.00 THEN Low
• WHEN price BETWEEN 10.00 AND 20.00 THEN
  Moderate
• WHEN price gt 20.00 THEN Expensive
• ELSE Unknown
• END
• FROM titles

5
Control of Flow

• BEGIN..END
• GOTO label..label
• IF..ELSE
• RETURN (n)
• WHILE..BREAK, WHILE..CONTINUE

6
Variables

• Local variables - _at_variable
• assign with SET, SELECT
• pass as stored procedure parameter
• declared using DECLARE statement
• System functions (global variables)
• _at__at_SPID
• _at__at_ROWCOUNT
• _at__at_IDENTITY
• _at__at_FETCH_STATUS
• _at__at_ERROR

7
String Functions

• CHARINDEX(string, expression)
• PATINDEX(pattern, expression)
• LEFT(expression, position), RIGHT(expression,
  position),
• LTRIM(expression), RTRIM(expression)
• SUBSTRING(expression, start, length)
• LEN(expression)
• CHAR(integer_expression)
• Also ASCII, DIFFERENCE, LOWER, NCHAR,
  QUOTENAME, REPLACE, REPLICATE, REVERSE, SOUNDEX,
  SPACE, STUFF, UNICODE, UPPER

8
Date Functions

• GETDATE()
• DATEADD(datepart, number, datetime)
• DATEDIFF(datepart, datetime1, datetime2)
• DATEPART(datepart, datetime)
• DATENAME(datepart, datetime)
• ex DATEDIFF(day, 12/09/1999, 12/25/1999)

9
Mathematical Functions

• ROUND(numeric_expr, length)
• RAND(seed)
• Also ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS,
  COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER,
  RADIANS, SIGN, SIN, SQRT, SQUARE, TAN

10
Miscellaneous Functions

• CAST(input parm AS desired_datatype)
• CONVERT(desired_datatype, input parm, style)
• CONVERT(char(10), getdate(), 101)
• STR(float , length, decimal_places)
• ISNULL(input parm, return_val_if_null)
• PRINT string_expression

11
CONVERT styles
12
Dynamic SQL

• EXEC(generated_string)
• ex
• DECLARE _at_SQLString varchar(8000)
• SET _at_SQLString SP_HELP
• EXEC(_at_SQLString)

13
Error Handling

• RAISERROR
• User-defined errors only (master..sysmessages)
• _at__at_Error
• reflects status of last statement executed
• RETURN overall success status

14
Cursors

• Use with caution - performance!
• Static snapshot in TempDB, read-only
• Keyset stores only keys in TempDB, must have
  unique index, data can change
• Dynamic can update, scrollable
• Forward-only fastest cursor, except for
• Fast Forward-only

15
Stored Procedures

• Use EXEC or EXECUTE to invoke
• Can call using positional or named parameters
• Can use optional and/or output parameters
• Use RETURN to pass the success status of the
  stored procedure call back to the calling code

16
Triggers

• inserted and deleted tables
• Executed only upon success of the update
  statement
• Can have multiple triggers per update type no
  guarantee of firing order
• Can define on Insert and/or Update and/or Delete
• Fire after update, only

17
Debugging TSQL Code

• PRINT text
• SELECT variables
• Debug flag
• VB and other MS development tools
• Must have Enterprise version
• Must install DLLs on SQL Server
• Other ISV software Sylvain Faust Intl

18
..and dont forget comments!

• /../ still acceptable
• -- use this instead, ex
• -- Welcome to my stored procedure
• -- Created on 12/09/1999
• -- Written by Erin Welker
• -- Sample call MyProc abc, 123

19
More Information

• System stored procedures
• ex sp_helptext sp_help
• SQL Books Online
• Learn Transact-SQL in 21 Days
• ????