1 / 19

Programming in Transact-SQL

What is Transact-SQL?. When do I use it?Stored ProceduresTriggersAdhoc queriesWhere do I use it?Query AnalyzerOSQLScripts DTS, SQL Agent, etc.. SELECT Statement - The Core of SQL. SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression]

erol
Download Presentation

Programming in Transact-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. 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 ????

More Related