Programming SECTION 7

1. ProgrammingSECTION 7 Procedures and Functions

2. Introduction • SQL is only a query language • SQL does not have features that allow sophisticated computations

3. Users need more sophisticated SQL-oriented programming capabilities • Microsoft SQL solution • Stored Procedures and Functions • Looking at basics

4. Stored Procedures • Lets you use all the SQL data manipulations • Fully SQL data types • Allows you to do sophisticated processing

5. The Front End • Lets you create SQL data manipulations • Can see results immediately • Also lets you use programming • The usual front-end displayed to the user

6. Stored Procedures and Functions • How can they be used? • Execute rapidly • Help you build complex business logic easily and in a modular fashion

7. Stored Procedures • Stored in the database • A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

8. IF (@QuantityOrdered < (SELECT QuantityOnHand FROM Inventory WHERE PartID = @PartOrdered) ) BEGIN -- SQL statements to update tables and process order. END ELSE BEGIN -- SELECT statement to retrieve the IDs of alternate items -- to suggest as replacements to the customer. END

9. Stored Functions • Designed to return a value used within a larger SQL statement • E.g. using max, or min • Functions cannot be used to make changes to the database, whereas stored procedures allow you to do inserts and updates, etc.

10. Triggers • EG: When users log on or off • Gets executed automatically • Need a triggering event

11. Basic Procedure Structure • Four sections • Header section (optional) • Declaration section (optional) • Execution section • Exception section (optional)

13. Header Section • Anonymous block header • The specification of the function -- ================== -- Author -- Create date: -- Description -- ================== DECLARE • Block labels make it easier to read codes

14. Declaration Section (Optional) • Ends at keyword • BEGIN • Starts with keyword • DECLARE • Contains declarations for • Variables, constants, cursors, exceptions • Function and procedures

15. DECLARE @num_a NUMERIC = 6, @num_b NUMERIC; • What are we doing here? • When a basic block has finished its run • Declarations stop existing

16. Execution Section • Ends with END • Starts with keyword • BEGIN

17. In our example: BEGIN try -- Generate a divide-by-zero error. SET @num_b = 0; SET @num_a = @num_a / @num_b; PRINT @num_a; END try BEGIN catch SELECT ERROR_MESSAGE() AS ErrorMessage; END catch The TRY statement lets you test a block of code for errors The CATCH statement lets you handle the error

18. Creating a Simple Procedure Create procedure test_proc as Select * from customer; • We will try the following code

21. Use • CREATE procedure_name • AS procedure_body

23. Calling Procedures or Functions Without any parameters Execute procedure_name; With formal parameters Execute procedure_name @alpha = 50, @bravo = 20; • A procedure or function may not have formal parameters or default values

24. Variables and Constants • Storing numbers • NUMERIC datatype • Storing text • CHAR, VARCHAR or nVARCHAR datatype • Variables: • Essentially containers with name tags

25. Unicode Characters • Unicode is a computing industry standard for the consistent encoding, representation and handling of text expressed in most of the world's writing systems. • Unicode's success at unifying character sets has led to its widespread and predominant use in the internationalization of computer software. • nVARCHAR can store any Unicode data • All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors can be a very difficult problem.

26. Declaring Variables • A valid variable_name • Up to 30 characters • Letters, 0-9, underscore(_), $, and # • Starts with @ • Cannot use a reserved word that is used by the DBMS • The syntax @variable_name datatype or @variable_name (datatype) = default_value_expression e.g. @num_b NUMERIC or @num_a NUMERIC = 6

27. Datetime2 • date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision. • Text • Variable-length non-Unicode data • ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them.

28. CAST The PRINT will print a numeric by itself if there are no other items to be printed. If there are two or more data types that are numeric you must use CAST or CONVERT. @new_price is numeric. Threfore CAST converts @new_price to a nvarchar datatype.

29. Notice the execution of a stored procedure within another procedure.

30. Using Variables • Hold results of computations • Return values • As actual values for calling functions or procedures • Basic units of programming

31. Control Structures • One can do conditional processing • Also can do iterations • Many times you may want to do one thing if something is true or something else if it is not true

32. IF Statement • The syntax: IF condition_1 BEGIN (SET) Actions_1 END ELSE IF condition_2 BEGIN (SET) Actions_2 END …. ELSE Actions_last

34. Create procedure CZP @birthyear numeric AS IF @birthyear IN(1900,1912,1924,1936,1948,1960,1972,1984,1996,2008) BEGIN PRINT 'You are a rat' END ELSE BEGIN PRINT 'You are not a rat' END

38. An example. Create a function that: • Compute discounts on orders • Input order amounts • Returns discount amount (zero for wrong inputs) • The RETURN keyword

39. * Notice the use of RETURNS * RETURNS the amount to the main procedure * These NUMERIC data types are set at (6,2) so that the result will be returned with two decimal places, with 6 significant digits.

41. LOOP • An iteration construct • The Syntax: Declare @counter int Set @counter = 1 While @counter < 10 Begin      print 'The counter is ' + cast(@counter as nvarchar)     Set @counter = @counter + 1 End

42. Note that counter is an integer and thus has to be converted. This example uses CAST

43. This example uses CONVERT

44. Declare @counter int Set @counter = 0 While @counter < 7 Begin     Select * from Chinese_Zodiac where rounding = @counter     Set @counter = @counter + 1 End Instead of select you can : Insert values Alter Delete

47. Notice that this is @counter + 2

48. Cursors • An extremely important programming construct . SQL statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set from an SQL query allows processing the result set one row at a time. • Stands for: • “Current set of records” • It allows you to take a subset of data and output the information in various ways.

49. The OPEN command • Opens the cursor for use • The FETCH command • Retrieves a specific row from a cursor • The CLOSE command • Closes an open cursor

50. Cursor Declaration • Declared in the following manner: • Declare variables to hold the output from the cursor. • Declare the cursor object. • Assign the query to the cursor. • Open the cursor and fetch the first row • Loop until there are no more results. In the loop print out the Customer Number and the Customer Last Name from the result set and fetch the net • Close the cursor. • Deallocate the cursor to free up any memory or open result sets.