Simple Programming Part 1 SECTION 7 Procedures and

Simple Programming Part 1 SECTION 7 Procedures and Functions

Introduction • T-SQL (SQL) does not have features that allow sophisticated computations • SQL is only a query language

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

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

The Front End • The usual front-end displayed to the user • Lets you create SQL data manipulations • Also lets you do programming

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

A Generic Example IF (@Quantity. Ordered < (SELECT Quantity. On. Hand FROM Inventory WHERE Part. ID = @Part. Ordered) ) 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

Stored Functions • Functions cannot be used to make changes to the database, whereas stored procedures allow you to do inserts and updates, etc. • A function is designed to return a value used within a larger SQL statemen

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

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

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

Declaration Section (Optional) • Starts with keyword – DECLARE • Ends at keyword – BEGIN • Contains declarations for • Variables, constants, cursors, exceptions

DECLARE @num_a NUMERIC = 6, @num_b NUMERIC; • What are we doing here? • When a procedure has finished executing – Declarations stop existing

Execution Section • Starts with keyword – BEGIN • Ends with END

Exception Section • Starts with keyword – BEGIN CATCH • Ends with END CATCH

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 Error. Message; END catch The TRY statement lets you test a block of code for errors The CATCH statement lets you handle the error

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

• Use • CREATE procedure_name • AS procedure_body CREATE PROCEDURE test_proc AS DECLARE @alpha n. VARCHAR(30) BEGIN SET @alpha = ‘HELLO WORLD’ PRINT @alpha END

Notice the different colouring

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

Variables • Variables: • Essentially containers with name tags • Storing numbers – NUMERIC datatype • Storing text – CHAR, VARCHAR, n. VARCHAR datatypes

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. • n. VARCHAR • All modern operating systems and development platforms use Unicode internally.

Declaring Variables • The syntax @variable_name datatype or @variable_name (datatype) = default_value_expression e. g. @num_b NUMERIC or @num_a NUMERIC = 6 @num_c NUMERIC(4, 2) • 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

• Datetime 2 • Text – non-Unicode

A Simple Procedure – calculates a percent price increase CREATE PROCEDURE increase_prices @old_price NUMERIC, @percent_increase NUMERIC = 5 AS DECLARE @new_price NUMERIC BEGIN SET @new_price = @old_price + @old_price * @percent_increase/100 PRINT 'New Price: $' + CAST(@new_price as n. VARCHAR) END

CREATE PROCEDURE increase_prices @old_price NUMERIC, @percent_increase NUMERIC = 5 AS DECLARE @new_price NUMERIC BEGIN SET @new_price = @old_price + @old_price * @percent_increase/100 PRINT 'New Price: $' + CAST(@new_price as n. VARCHAR) END

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. Therefore CAST converts @new_price to a nvarchar datatype.

CONVERT Works like CAST but structured differently

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

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

• Want to develop a procedure to calculate your Chinese birth sign. Zodiac Sign YEAR OF BIRTH Rat 2008 1996 1984 1972 1960 1948 1936 1924 1912 1900 Bull 2009 1997 1985 1973 1961 1949 1937 1925 1913 1901 Tiger 2010 1998 1986 1974 1962 1950 1938 1926 1914 1902 Rabbit 2011 1999 1987 1975 1963 1951 1939 1927 1915 1903 Dragon 2012 2000 1988 1976 1964 1952 1940 1928 1916 1904 Snake 2013 2001 1989 1977 1965 1953 1941 1929 1917 1905 Horse 2014 2002 1990 1978 1966 1954 1942 1930 1918 1906 Goat 2015 2003 1991 1979 1967 1955 1943 1931 1919 1907 Monkey 2016 2004 1992 1980 1968 1956 1944 1932 1920 1908 Rooster 2017 2005 1993 1981 1969 1957 1945 1933 1921 1909 Dog 2018 2006 1994 1982 1970 1958 1946 1934 1922 1910 Pig 2019 2007 1994 1983 1971 1959 1947 1935 1923 1911

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

CREATE PROCEDURE Chinese_Zodiac_Proc (@Birth. Date NUMERIC) --This procedure deals with birth years and the Chinese Zodiac AS PRINT ('*****'); IF @Birth. Date IN( 1924, 1936, 1948, 1960, 1972, 1984, 1996) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Rat. ') END ELSE IF @Birth. Date IN( 1925, 1937, 1949, 1961, 1973, 1985, 1997) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Bull. ') END ELSE IF @Birth. Date IN( 1926, 1938, 1950, 1962, 1974, 1986, 1998) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Tiger. ') END ELSE IF @Birth. Date IN( 1927, 1939, 1951, 1963, 1975, 1987, 1999) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Rabbit. ') END ELSE IF @Birth. Date IN( 1928, 1940, 1952, 1964, 1976, 1988, 2000) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Dragon. ') END ELSE IF @Birth. Date IN( 1929, 1941, 1953, 1965, 1977, 1989, 2001) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Snake. ') END ELSE IF @Birth. Date IN( 1930, 1942, 1954, 1966, 1978, 1990, 2002) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Horse. ') END ELSE IF @Birth. Date IN( 1931, 1943, 1955, 1967, 1979, 1991, 2003) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Sheep. ') END ELSE IF @Birth. Date IN( 1932, 1944, 1956, 1968, 1980, 1992, 2004) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Monkey. ') END ELSE IF @Birth. Date IN( 1933, 1945, 1957, 1969, 1981, 1993, 2005) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Rooster. ') END ELSE IF @Birth. Date IN( 1934, 1946, 1958, 1970, 1982, 1994, 2006) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Dog. ') END ELSE IF @Birth. Date IN( 1935, 1947, 1959, 1971, 1983, 1995, 2007) BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @Birth. Date) + ', the year of the Pig. ') END ELSE BEGIN PRINT (' Your birth date is out of the program range!') END PRINT ('*****')

Now to develop the procedure using a calculation for birth sign 1944/12 1945/12 1946/12 1947/12 1948/12 1949/12 1950/12 1951. 12 1952/12 1953/12 1954/12 1955/12 1956/12 = = = = 162. 00 162. 083333 162. 166667 162. 25 162. 33333 162. 416667 162. 583333 162. 666667 162. 75 162. 833333 162. 916667 163. 00 MONKEY ROOSTER DOG PIG RAT BULL TIGER RABBIT DRAGON SNAKE HORSE SHEEP MONKEY

Therefore: Sign Remainder (R) R x 12 MONKEY 0 0 ROOSTER 0. 083333 1 DOG 0. 166667 2 PIG 0. 25 3 RAT 0. 33333 4 BULL 0. 416667 5 TIGER 0. 5 6 RABBIT 0. 583333 7 DRAGON 0. 666667 8 SNAKE 0. 75 9 HORSE 0. 833333 10 SHEEP 0. 916667 11

Thus have to calculate the remainder for each birth year Use @birthyear = 1950 Declare three variables: @X NUMERIC(10, 6) @Y NUMERIC(4) @Z NUMERIC(3, 2) Now SET @X =@birthyear/12 SET @Y = @birthyear/12 (162. 5) (162) SET @Z = @X - @Y (0. 50)

Only need two significant digits Multiply by 12. Gets rid of decimal points

CREATE PROCEDURE Chinese_Zodiac_Proc 02 (@birthyear NUMERIC) --This procedure deals with birth years and the Chinese Zodiac AS DECLARE @X NUMERIC(10, 6), @Y NUMERIC(4), @Z NUMERIC(2) SET @X = @birthyear/12 SET @Y = @birthyear/12 BEGIN IF @Y > @X SET @Y = @Y-1 SET @Z = (@X - @Y)*12 END PRINT ('*****'); IF @Z = 0 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Monkey. ') END ELSE IF @Z = 1 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Rooster. ') END ELSE IF @Z = 2 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Dog. ') END ELSE IF @Z = 3 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Pig. ') END ELSE IF @Z = 4 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Rat. ') END ELSE IF @Z = 5 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Bull. ') END ELSE IF @Z = 6 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Tiger. ') END ELSE IF @Z = 7 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Rabbit. ') END ELSE IF @Z = 8 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Dragon. ') END ELSE IF @Z = 9 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Snake. ') END ELSE IF @Z = 10 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Horse. ') END ELSE IF @Z = 11 BEGIN PRINT ('You were born in ' + CONVERT(n. VARCHAR(20), @birthyear) + ', the year of the Sheep. ') END PRINT ('*****')

How can @Y be greater than @X? Correction if @Y > @X No correction if @Y > @X @Z is negative @Z is positive

There is a problem with BCE year as there is no year zero. If the procedure uses ‘ 0’, the answer is: **** You were born in 0, the year of the Monkey **** This is incorrect as 1 BCE is the year of the Monkey This is corrected in the next slide

Correction for year 0 As there is no year 0 you have to increment any BCE years by 1

WHILE 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 OR: Declare @counter int = 1 While @counter < 10 Begin print 'The counter is ' + cast(@counter as nvarchar) Set @counter = @counter + 1 End

• Will use the following table: 1. For another loop example; and 2. For a discussion with CURSORS. CREATE TABLE CHINESE_ZODIAC (Number NUMERIC(2) CONSTRAINT cz_pk PRIMARY KEY, Zodiac. Sign n. VARCHAR(10)) INSERT INTO CHINESE_ZODIAC VALUES (0, 'Monkey') INSERT INTO CHINESE_ZODIAC VALUES (1, 'Rooster') INSERT INTO CHINESE_ZODIAC VALUES (2, 'Dog') INSERT INTO CHINESE_ZODIAC VALUES (3, 'Pig') INSERT INTO CHINESE_ZODIAC VALUES (4, 'Rat') INSERT INTO CHINESE_ZODIAC VALUES (5, 'Bull') INSERT INTO CHINESE_ZODIAC VALUES (6, 'Tiger') INSERT INTO CHINESE_ZODIAC VALUES (7, 'Rabbit') INSERT INTO CHINESE_ZODIAC VALUES (8, 'Dragon') INSERT INTO CHINESE_ZODIAC VALUES (9, 'Snake') INSERT INTO CHINESE_ZODIAC VALUES (10, 'Horse') INSERT INTO CHINESE_ZODIAC VALUES (11, 'Goat')

Simple example of using a While Loop Declare @counter int Set @counter = 0 While @counter < 7 Begin Select * from Chinese_Zodiac where rounding = @counter Set @counter = @counter + 1 End

GOTO LOOP • Once again an iteration construct • The Syntax: DECLARE @counter INTEGER = 0 EPSILON: BEGIN PRINT 'Hello World' SET @counter = @counter + 1 END IF @Counter < 11 GOTO EPSILON

Problems with GOTO According to Microsoft: Use GOTO statement sparingly. Excessive use of the GOTO statement adds difficult to understand the logic of the T-SQL batch. You can almost always implement the logic using other control-of-flow statements. According to Wikipedia: Spaghetti code is a derogatory term for source code that has a complex and tangled control structure, especially one using many GOTO statements, exceptions, threads, or other unstructured branching constructs. It is named such because program flow is conceptually like a bowl of spaghetti, i. e. twisted and tangled. Spaghetti code can be caused by several factors, such as continuous modifications by several people over a long life cycle. Structured programming greatly decreases the incidence of spaghetti code.

Spaghetti Code

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

See Part 2 Now