1	Program SQL Stored Procs, User-Defined Functions Rick Dobson, MCP, Ph.D. CAB, Inc. rick@cabinc.net www.programmingmsaccess.com
2	About the Topic This presentation drills down on programming SQL Server stored procs and user-defined functions (udfs) You learn the syntax for creating stored procs and udfs with T-SQL You learn how to programmatically invoke them with ADO.NET and VB.NET You learn how to Program input and output parameters Return status values Return row sets
3	About the Presenter Rick Dobson is an author, trainer, and webmaster He focuses on Microsoft database topics 6 books 3 DVDs His practice sponsored 4 annual seminar presentation series These seminars were presented across the US On the web in a webcast format with an 800# for 2-way communication Rick’s website (www.programmingmsaccess.com) Features content on SQL Server, VB.NET, ADO.NET, Access, and VBA His seminar presentations Serves over 1.1 million page views annually
4	Why use stored procs and user-defined functions You can reference stored procs and user-defined functions (udfs) from a server Using parameters with stored procs and udfs increases their flexibility for Inputting values Retrieving values Executing commands, such as inserts or deletes Users can have permissions to run stored procs and udfs without having permissions for the underlying objects
5	Contrasting stored procs vs. user-defined functions Stored procs Return result sets and perform commands Have input parameters, output parameters, return status values Cannot be used as an argument for FROM clause Udfs Have scalar or table-valued return values Accept input parameters Can be used as an argument for a FROM clause – this kind of udf acts like a parametric view You can develop and evaluate stored procs and user-defined functions with T-SQL in Query Analyzer
6	Structure for CREATE PROC statement CREATE PROC procname Comma-delimited parameters with Name Data Type Optionally, Direction (implicitly input) Optionally, Default value AS followed by T-SQL, such as SELECT statement SET statement Other T-SQL statements
7	Demo: Create upDis_ContinuedProducts Start by conditionally invoking DROP PROC statement Invoke CREAT PROC statement For parameters note @parametername Data type Optional Default value assignment Optional Direction assignment
8	Demo: Create upDis_ContinuedProducts (Cont’d) SELECT statement Has FROM and WHERE clause FROM designates source tables WHERE Indicates join with equality of values between tables Uses parameters in criterion assignment statements @@ROWCOUNT returns rows affected RETURN keyword assigns return status value
9	Demo: Run stored proc with default parameter value Designate local variables with DECLARE EXEC statement runs a stored proc Omitting parameter with a default value causes use of default value Assign stored proc to local variable to recover return status value Specify input value with assignment to parameter name Recover output parameter by assigning local variable to it with the OUTPUT keyword
10	Demo: Run stored proc with non-default parameter value Include local variable declaration for parameter with default value SET statement assigns local variable value for parameter assignment IF…ELSE statement controls string value in summary of results Include parameter with default value in list for stored proc; if you don’t, SQL Server uses the default value
11	Creating a UDF Use DROP FUNCTION functionname and CREATE FUNCTION functionname Specify arguments in parentheses after function name RETURNS clause declares the data type for the return value from a udf After RETURNS clause use the AS keyword Place T-SQL statements for the body between BEGIN and END keywords Return a scalar with an aggregate function in a SELECT statement; specify a non-table data type in RETURNS clause, such as int Return a row set with a SELECT statement and specify a table data type in he RETURNS clause
12	1 Stored Proc = 3 UDFs A single udf can return just one result (scalar or table) Therefore, to return three separate results (a result set, output parameter, and return status value), you need three separate udfs Two advantages of udfs vs. stored procs is that Udfs build simplicity into in your applications Udfs make code more re-usable by forcing the building of smaller code blocks
13	Demo: UDF to Count the Products in a Category INFORMATION_SCHEMA.ROUTINES can verify existence of a udf, such as ufProductsInCategory, in a database DROP FUNCTION can remove it CREATE FUNCTION demos Syntax and use of input parameter RETURNS clause syntax Use of aggregate function to return a scalar value (count of products in a category)
14	Demo: Count the Discontinued or Continued Products in a Category ufDis_ContinuedProductsInCategory returns count of discontinued or continued products Use of bit data type permits the specification of a Boolean data type (0 or 1) Two parameters control the type of values over which to aggregate Bit data type counts over discontinued (1) or continued (0) products Int data type determines the product category for which to count products
15	Demo: Return a result set ufDis_ContinuedProductRowsInCategory illustrates the syntax for a table-valued udf RETURNS clause must specify table RETURN clause contains a SELECT statement that does not generate an aggregate value The SELECT statement can contain multiple parameters, such as for the value of Discontinued CategoryID
16	Demo: Test UDFs Must specify user name, such as dbo, when designating scalar udfs User name qualifier is not required for a table-valued udf If a udf parameter has a default parameter value, you must still specify it either with the Default keyword Value for the parameter
17	Demo: Conditionally Insert a Row uspConditionalInsert conditionally inserts a new row to the Shippers table if another row does not have the same CompanyName column value Stored proc takes two parameters @CompanyName @Phone ShipperID is generated at the server Before insert attempt, a SELECT statement determines if row with @CompanyName is in the Shippers table
18	Demo: Conditionally Insert a Row (Cont’d) If row with CompanyName column value of @CompanyName exists Code does not insert a new row Return status value set to 0 If row with CompanyName column value of @CompanyName does not exist Code inserts new row with @CompanyName and @Phone column values Return status value is set to ShipperID of new row Test with T-SQL that Assigns values to @CompanyName and @Phone Invoke stored proc and recover return status value
19	Demo: Create and Test a Stored Proc to Delete a Row uspDeleteShipperID deletes a row designated by its ShipperID column value The @ShipperID parameter specifies the ShipperID column value Client test T-SQL code Assigns a value to the @ShipperID local variable Performs a SELECT statement against the Shippers table Deletes the row Performs another SELECT statement
20	Form1 in the MSDNBootCamp VB.NET Project
21	Controls on Form1 TextBox1 lets a user set the @CategoryID parameter value RadioButton1 and RadioButton2 enable setting of @Dis_Con parameter value A click to Button1 invokes the upDis_ContinuedProducts stored proc Label2 (above ListBox1) shows the number of discontinued/continued products within the total products within a category ListBox1 shows the discontinued/continued products in a product category
22	Demo: Code behind Form1 Start by making a SqlConnection to MSDNBootCamp database Cmd1 SqlCommand object Instantiate object pointing at the upDis_ContinuedProducts stored proc Use the CommandType property to indicate that you are pointing at a stored proc
23	Demo: Code behind Form1 (Cont’d) Instantiate 4 parameters and assign values to three of them Prm1 points at @CategoryID Prm2 points at @ProductCount, with an output direction Prm3 points at @Dis_Con Prm4 Has an arbitrary name @NumberOfProducts Recovers the return status value
24	Demo: Code behind Form1 (Cont’d) The ExecuteReader method populates the rdr1 SqlDataReader with the result set from upDis_ContinuedProducts Do loop passes through rdr1 rows and assigns them to ListBox1 RadioButton1, prm2, and prm4 combine to set the label above ListBox1 Prm2 in an If…Then…Else statement makes ListBox1 invisible if there are no products to show
25	Form2 in the MSDNBootCamp VB.NET Project
26	Controls on Form2 TextBox1 lets a user set the @CategoryID parameter value RadioButton1 and RadioButton2 enable setting of @Dis_Con parameter value A click to Button1 invokes three udfs ufProductsInCategory ufDis_ContinuedProductsInCategory ufDis_ContinuedProductRowsInCategory Label2 (above ListBox1) shows the number of discontinued/continued products within the total products within a category DataGrid1 shows the discontinued/continued products and their category name
27	Demo: Code behind Form2 Start by making a SqlConnection to MSDNBootCamp database Cmd1 SqlCommand object Gets re-used with all three udfs Prm1 (@CategoryID) gets re-used in all three udfs Prm2 (@Dis_Con) gets re-used in two udfs bytDis_Con is a local variable that Receives its value for radio buttons on Form2 Assigns a value to prm2 Controls the label above DataGrid1
28	Demo: Code behind Form2 (Cont’d) Code returns udf values with a SELECT statement with a SqlCommand object Remember to use dbo qualifier for scalar udfs Invoke the ExecuteScalar method for a SqlCommand object to return a scalar value To return a table from a udf Assign the SqlCommand object to the to a SqlDataAdapter Fill a DataSet with the SqlDataAdpater Bind the DataSet to a DataGrid to show the table easily
29	Form3 in the MSDNBootCamp VB.NET Project
30	Controls on Form3 TextBox1, TextBox2, and TextBox3 TextBox2 and TextBox3 are for inputting @CompanyName and @Phone parameters TextBox1 is for returning the return status value from uspConditionalInsert with the ShipperID column value for a successfully inserted row Button1 invokes the uspConditionalInsert stored proc If insert succeeds, TextBox1 can get an updated value If insert fails, a message box provides feedback about the problem Button2 invokes the uspDeleteShipperID
31	Demo: Code behind Form3 Module-level declarations makes SqlConnection and SqlCommand objects available to multiple procedures Load event procedure Assigns a connection string to the form Initially populates TextBox1 through TextBox3 Formats controls on the form
32	Demo: Code behind Form3 (Cont’d) Button1_Click Assigns uspConditionalInsert to a SqlCommand object instantiated at the module level Assigns properties to three parameters declared within the procedure Uses a SqlDataReader to capture existing column values for a previously existing row having the proposed CompanyName column value for a new row If the insert succeeds, the return status value (prm1) is assigned to TextBox1.Text Button2_Click Assigns uspDeleteShipperID to a SqlCommand object instantiated at the module level Demonstrates the use of the ExecuteNonQuery method to run the stored proc
33	Summary You learned why and how to develop stored procs and udfs with T-SQL Special emphasis was given to programming parameters and return status values for stored procedures and udfs You also learned the differences in syntax and use for scalar udfs and table-valued udfs ADO.NET samples demonstrated how to work with stored procs and udfs using such classes as SqlCommand SqlDataReader SqlDataAdapter and DataSet