Programming in Transact-SQL

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_list[INTO new_table_]FROM table_source[WHERE search_condition][GROUP BY group_by_expression][HAVING search_condition][ORDER 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 < 10.00 THEN ‘Low’ WHEN price BETWEEN 10.00 AND 20.00 THEN ‘Moderate’ WHEN price > 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 - @variable assign with SET, SELECT pass as stored procedure parameter declared using DECLARE statement System functions (global variables) @@SPID @@ROWCOUNT @@IDENTITY @@FETCH_STATUS @@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 @SQLString varchar(8000) SET @SQLString = “SP_HELP” EXEC(@SQLString)

13. Error Handling RAISERROR User-defined errors only (master..sysmessages) @@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 Int’l

18. ..and don’t 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 ????