22 apr RS Sprocs FINAL by sergey9999 sergey9999

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

SQL Server Stored Procedures: the Whys and the Hows Welcome to a tour on a common topic: stored procedures. Stored procedures are available with most server-level databases, such as Oracle and Microsoft SQL Server. Today, we're going to look at what they are, how they're advantageous, and how we make use of them. You will need access to an empty SQL Server database. This could be on your own SQL Server, or on your ISP's. Alternatively, I highly suggest you get MSDE 2000, a limited-performance version of SQL Server, which is runs on Windows 2000 Professional and Windows XP and is free. Which ever option you choose, you will need to install the SQL Server Client Tools (more specifically, SQL Query Analyzer) which comes with any SQL Server installation file set, including MSDE 2000. In the source code zip file (available from the 'download link'), SPSample.sql contains all the (autogenerated) SQL statements that create a sample database, including stored procedures. Simply open this file with SQL Query Analyzer and press F5 to execute it. The ASP files in the zip contain 'before' and 'after' scenarios using this database, first with ad-hoc queries, then with ad-hoc stored procedure usage, then with the correct stored procedure usage.

What are they exactly? First, a bit of background: when your SQL statement is received by the database engine (from ASP through the ADO components), it first parses the query and creates an 'execution plan' for running the query. At this stage, all the validity checking is done and the order in which it retrieves and collates data is determined. Once this execution plan is assembled, it is then executed step by step, until complete. It then returns the resulting recordset (if appropriate) to ASP via ADO (Active Data Objects). To improve database performance, engineers realized that common queries used the same execution plan. It was a natural step to pre-compile these execution plans and provide for a way to pass values in to these execution plans just before execution. These execution plans, of course, are stored procedures. Stored procedures are kept on the database server. On creation, stored procedures are given names to reference them by. Optionally, parameters can be specified, each with a name and data type (such as INT or VARCHAR). Then, when retrieving data, we simply query the stored procedure (by its name) along with values for each parameter. This way, we can create re-usable queries and reduce duplicate code.

Why are they better than plain ole SQL? It might seem like extra effort to go through, creating these stored procedures. However, they provide immense benefits: -

Pre-compiled – Execution plan is determined once (on creation), and stored on the server Strongly-typed parameters – numbers are numbers, strings are strings Secure – no table information in the ASP code - one step closer to preventing SQL injection attacks Modular – promotes reuse of logic, as we can now reference a pre-written query by name again and again Allow for multiple operations in one database call Abstraction – if the database table structure changes, only need to modify a stored procedure once as opposed every time the query is used in ASP Encapsulation – allows us to keep the basic data logic in the database instead of in the ASP. This way, the ASP code is simplified. Copyright © 2004 DMXzone.com All Rights Reserved to get more go to DMXzone.com

Page 1 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

On top of all of that, you can set your database permissions in such a way that your ASP scripts only have access to the stored procedures and not to the database's tables directly. This takes us one more step closer to being 100% safe from SQL injection attacks.

How do we use stored procedures? The best (free) tool for developing stored procedures is the SQL Query Analyzer. It allows you to browse all the databases on your SQL Server, and view database tables, views, stored procedures, and so forth. Working with stored procedures is easy with this tool, as you can easily browse to and edit any stored procedure in your database. You can also directly browse and edit data in the database tables.

Creating stored procedures A stored procedure looks like a normal ad-hoc query; in a stored procedure, the query is prefixed with some code for defining the procedure's name and parameters. Here's the basic syntax for creating stored procedures: CREATE PROCEDURE [procedure_name] AS [query] For example: CREATE PROCEDURE GetTopFiveProducts AS SELECT TOP 5 ProductName FROM Product ORDER BY PurchaseCount DESC That will work for any queries which don't require values to filter by. The following syntax allows us to define what parameters can be passed into the SP. We define a parameter by prefixing the name with an @ symbol, followed by a valid SQL Server data type, with a field size in parenthesis if appropriate: CREATE PROCEDURE [procedure_name] ( @[parameter_name] [parameter_type], @[parameter_name] [parameter_type] ) AS [query] For example: CREATE PROCEDURE SearchProductsByCategory ( @CategoryID INT, @Keyword NVARCHAR(50) ) Copyright ÂŠ 2004 DMXzone.com All Rights Reserved to get more go to DMXzone.com

Page 2 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

AS SELECT ProductName FROM Product WHERE CategoryID = @CategoryID AND Description LIKE '%' + LTRIM( RTRIM( @Keyword ) ) + '%' As stored procedures are stored on the server on creation, we need to be able to modify them somehow. This is easy to do; all we do is replace the CREATE keyword with the ALTER keyword. SQL Server will return an error if you try create the same procedure twice, so be sure to change CREATE to ALTER once you've successfully created the procedure for the first time.

Executing stored procedures Now that we know how to create them, let's see how we use them. Here's the syntax: EXECUTE [procedure_name] Or EXECUTE [procedure_name] [parameter value], '[parameter value]' Here are the corresponding uses of the above sample Sprocs: EXECUTE GetTopFiveProducts EXECUTE SearchProductsByCategory 1, 'optical' As you've no doubt seen, text or string parameters are passed in single quotes. It's important to note that you aren't limited to a single SQL query in a stored procedure. You can perform multiple queries in a stored procedure, including executing other stored procedures. This further enables us to employ object oriented programming techniques.

Using stored procedures in ASP Let's look at a couple queries from our SPSample_Adhoc.asp file. We'll convert them to stored procedures, and update the ASP code to execute these procedures. The first query retrieves a list of contacts, including contact ID, full name, email address, and yes/no value indicating whether the contact is active. Here it is: SELECT ContactID, FirstName, LastName, EmailAddress, IsActive FROM Contact ORDER BY LastName, FirstName In our ASP file, it looks like this: Copyright ÂŠ 2004 DMXzone.com All Rights Reserved to get more go to DMXzone.com

Page 3 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

var rs = Server.CreateObject("ADODB.Recordset"); rs.ActiveConnection = MM_SPSample_STRING; rs.Source = "SELECT ContactID, FirstName, LastName, EmailAddress, IsActive FROM Contact ORDER BY LastName, FirstName"; rs.CursorType = 0; rs.CursorLocation = 2; rs.LockType = 1; rs.Open(); So, to turn this into a SP, we add the CREATE PROCEDURE command before the query, and assign GetContacts as a name: CREATE PROCEDURE GetContacts AS SELECT ContactID, FirstName, LastName, EmailAddress, IsActive FROM Contact ORDER BY LastName, FirstName And we're done! We execute the SQL and our procedure is stored. We can now use it like this: EXECUTE GetContacts This of course will return a recordset containing contacts. We update the corresponding ASP: var rs = Server.CreateObject("ADODB.Recordset"); rs.ActiveConnection = MM_SPSample_STRING; rs.Source = "EXECUTE GetContacts"; rs.CursorType = 0; rs.CursorLocation = 2; rs.LockType = 1; rs.Open(); As you can see the ASP looks a bit cleaner! Let's look at a slightly more complicated query. This query is used to search for contact names by keyword. It compares a given keyword to both FirstName and LastName using the LIKE statement. Here it is, with a question mark representing where we concatenate the given keyword into the query: SELECT ContactID, FirstName, LastName, EmailAddress, IsActive FROM Contact WHERE FirstName LIKE '%' + ? + '%' OR LastName LIKE '%' + ? + '%' ORDER BY LastName, FirstName And in ASP: var rs = Server.CreateObject("ADODB.Recordset"); rs.ActiveConnection = MM_SPSample_STRING; rs.Source = "SELECT ContactID, FirstName, LastName, EmailAddress, IsActive FROM Contact WHERE FirstName LIKE '%" + searchKeyword + "%' OR LastName LIKE '%" + searchKeyword + "%' ORDER BY LastName, FirstName"; Copyright ÂŠ 2004 DMXzone.com All Rights Reserved to get more go to DMXzone.com

Page 4 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

rs.CursorType = 0; rs.CursorLocation = 2; rs.LockType = 1; rs.Open(); How eye-watering! Stored procedures allow us to use plenty of white space for our queries, as seen below. SELECT ContactID, FirstName, LastName, EmailAddress, IsActive FROM Contact WHERE FirstName LIKE ( '%' + ? + '%' ) OR LastName LIKE ( '%' + ? + '%' ) ORDER BY LastName, FirstName It's messy to do this in ASP as we have to make use of string delimiters (") and string concatenation (+). Let's convert to a stored procedure and clean that ASP up! As above, so below – we add the CREATE PROCEDURE statement, this time, including a definition for a SearchKeyword parameter. The parameter's type is NVARCHAR(50), which is fancy-speak for Unicodecompatible (that's the N) variable-length characters, with a determined length of 50 characters. Here's what it looks like: CREATE PROCEDURE SearchForContacts ( @SearchKeyword NVARCHAR(50) ) AS And then insert our SearchKeyword parameter where required: CREATE PROCEDURE SearchForContacts ( @SearchKeyword NVARCHAR(50) ) AS SELECT ContactID, FirstName, LastName, EmailAddress, IsActive FROM Contact WHERE FirstName LIKE ( '%' + @SearchKeyword + '%' ) Copyright © 2004 DMXzone.com All Rights Reserved to get more go to DMXzone.com

Page 5 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

OR LastName LIKE ( '%' + @SearchKeyword + '%' ) ORDER BY LastName, FirstName As you probably expected, executing the procedure is simple: EXECUTE SearchForContacts 'Jones' And as you probably also expected, the ASP looks a lot cleaner: var rs = Server.CreateObject("ADODB.Recordset"); rs.ActiveConnection = MM_SPSample_STRING; rs.Source = " EXECUTE SearchForContacts '" + searchKeyword + "'"; rs.CursorType = 0; rs.CursorLocation = 2; rs.LockType = 1; rs.Open(); So! We're starting to form a distinct separation between the code that manipulates the database and the code that uses it to perform other tasks like rendering HTML and processing HTTP request data. Although, we can take it one step further. At the moment, we're still using an ad-hoc method to execute these stored procedures; in that we're using string concatenation to put the parameter values with the EXECUTE command. Doing it this way is still totally vulnerable to SQL injection attacks, and although the code is now simpler, we aren't yet taking full advantage of the strong typing feature of the parameters (it's all still literal character data, because of the string concatenation). Strong-typing means that we say what type of data we're storing along with the data itself. Thus, instead of just saying 'it's a variable with the value of 5', we now say 'it's an integer with the value of 5'. Because it is the Command object that allows use of these strong-typed parameters, we will need to use Commands for both retrieval (SELECT) and modification (INSERT, UPDATE, DELETE) queries. This is possible, as the Command object's Execute method will return a Recordset implicitly if its CommandText property contains a SELECT query when it is called. For example: var rs = Server.CreateObject("ADODB.Recordset"); rs.ActiveConnection = MM_SPSample_STRING; rs.Source = "EXECUTE GetContacts"; rs.CursorType = 0; rs.CursorLocation = 2; rs.LockType = 1; rs.Open(); Is functionally equivalent to: var cmd = Server.CreateObject("ADODB.Command"); cmd.ActiveConnection = MM_SPSample_STRING; cmd.CommandText = "EXECUTE GetContacts"; cmd.CommandType = 1; cmd.CommandTimeout = 0; Copyright ÂŠ 2004 DMXzone.com All Rights Reserved to get more go to DMXzone.com

Page 6 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

cmd.Prepared = true; var rs = cmd.Execute(); To have this command work with a stored procedure, we need change the values of two properties: CommandText and CommandType. CommandText's value simply becomes the name of the stored procedure, and CommandType's value changes from 1, represented by the ADO constant adText, to 4, represented by the ADO constant adStoredProcedure: var cmd = Server.CreateObject("ADODB.Command"); cmd.ActiveConnection = MM_SPSample_STRING; cmd.CommandText = "GetContacts"; cmd.CommandType = 4; cmd.CommandTimeout = 0; cmd.Prepared = true; var rs = cmd.Execute(); See how suddenly it doesn't look so messy?

Making use of strongly typed parameters Strongly-typed parameters are represented by the ADO Parameter object. We can create a parameter like this: var keywordParam = Server.CreateObject("ADODB.Parameter"); keywordParam.Name = "SearchKeyword"; keywordParam.Type = 200; // 200: adVarChar (adojavas.inc constant) keywordParam.Value = searchKeyword; As you can see, we instantiate an ADODB.Parameter object, for which we set three properties: Name, Type, and Value. The Name property is the name given to the parameter in your stored procedure, without the @ symbol prefix. Likewise, the Value property simply gets the value you wish to pass to the stored procedure by that name. The Type value is the magic bit: it allows us to specify the parameter's type, which can be any of the SQL Server data types. These values are represented by various ADO constants, all of which can be found at http://www.devguru.com/Technologies/ado/quickref/ado_objects_index.html. I prefer to use the numerical values themselves. You'll see why in the next tutorial! Once we have a parameter configured, we add it to the command object via its Parameters collection's Append method. Here's how: var cmd = Server.CreateObject("ADODB.Command"); cmd.ActiveConnection = MM_SPSample_STRING; cmd.CommandText = "SearchForContacts"; cmd.CommandType = 4; cmd.CommandTimeout = 0; cmd.Prepared = true; cmd.Parameters.Append( keywordParam ); var rs = cmd.Execute(); The Command object has a 'short-hand' method for creating parameters: CreateParameter. The syntax is as follows: Copyright ÂŠ 2004 DMXzone.com All Rights Reserved to get more go to DMXzone.com

Page 7 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

var param = cmd.CreateParameter( name[, type[, paramDirection[, length[, value ]]]]] ); For example, here's our keyword parameter using CreateParameter: var keywordParam = cmd.CreateParameter( "SearchKeyword", 200, 1, 50, searchKeyword ); Note that even though it is the command object that has this method, you still need to use the Append method described above to add it to the command. The arguments for CreateParameter are: -

Name: the name of the parameter; corresponds with the parameter name in the stored procedure. Type: the data type of the parameter. Parameter direction: specifies whether a value is being passed to the stored procedure, from the stored procedure, or both. Length: the length of the parameter. Value: the value of the parameter.

Regarding parameter direction, possible values are adParamInput (1), adParamOutput (2), or adParamInputOutput (3). To make use of output or input-output parameters, we need to change our stored procedure slightly. For instance, in the following stored procedure, we have specified that the ContactID parameter allow for output by including the OUTPUT keyword in its definition. Note that doing this will make both output and input-output parameters function correctly. Here's the code: CREATE PROCEDURE AddContact ( @FirstName NVARCHAR(50), @LastName NVARCHAR(50), @EmailAddress NVARCHAR(50), @IsActive BIT, @ContactID INT OUTPUT ) AS Then, in the body of the stored procedure, we use SELECT to transfer a value into the parameter, in this case, the newly inserted ContactID for the Contact table. SQL Server provides a shortcut for this, @@IDENTITY, which contains the most recently generated 'auto number' value: INSERT INTO Contact ( FirstName, LastName, EmailAddress, IsActive ) VALUES ( @FirstName, @LastName, @EmailAddress, @IsActive ) SELECT @ContactID = @@IDENTITY This allows us to get single values from the database without using all that recordset overhead.

Page 8 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

But, this looks so complicated! Some might say so. For instance, what follows is an ASP code listing for a couple queries using the ad-hoc method. After that, the same queries are executed using stored procedures and parameters. See how they differ. Before: // Get Contacts var rs = Server.CreateObject("ADODB.Recordset"); rs.ActiveConnection = MM_SPSample_STRING; rs.Source = "SELECT ContactID, FirstName, LastName, EmailAddress, IsActive FROM Contact ORDER BY LastName, FirstName"; rs.CursorType = 0; rs.CursorLocation = 2; rs.LockType = 1; rs.Open(); // Update Contact var cmd = Server.CreateObject("ADODB.Command"); cmd.ActiveConnection = MM_SPSample_STRING; cmd.CommandText = "UPDATE Contact SET FirstName = '" + firstName + "', LastName = '" + lastName + "', EmailAddress = '" + emailAddress + "', IsActive = " + isActive + " WHERE ContactID = " + contactID; cmd.CommandType = 1; cmd.CommandTimeout = 0; cmd.Prepared = true; cmd.Execute(); And after: // Get Contacts var cmd = Server.CreateObject("ADODB.Command"); cmd.ActiveConnection = MM_SPSample_STRING; cmd.CommandText = "GetContacts"; cmd.CommandType = 4; cmd.CommandTimeout = 0; cmd.Prepared = true; var rs = cmd.Execute(); // Update Contact var cmd = Server.CreateObject("ADODB.Command"); cmd.ActiveConnection = MM_SPSample_STRING; cmd.CommandText = "UpdateContact"; cmd.CommandType = 4; cmd.CommandTimeout = 0; cmd.Prepared = true; cmd.Parameters.Append( cmd.CreateParameter( "FirstName", 200, 1, 50, firstName ) ); cmd.Parameters.Append( cmd.CreateParameter( "LastName", 200, 1, 50, lastName ) ); cmd.Parameters.Append( cmd.CreateParameter( "EmailAddress", 200, 1, 50, emailAddress ) ); cmd.Parameters.Append( cmd.CreateParameter( "IsActive", 11, 1, 1, isActive ) ); cmd.Parameters.Append( cmd.CreateParameter( "ContactID", 3, 1, 4, contactID ) ); cmd.Execute();

Page 9 of 10

SQL Server Stored Procedures: the Whys and the Hows Robert Stuttaford

I'll admit that this does look a bit more complicated. However, it's worth knowing that the capability is there, and it working with SQL Servers in this way will help you understand the way this stuff was intended to be used.

Conclusion Today we saw how stored procedures allow us to neatly separate SQL code and ASP code, and how to create and execute them. We explored how strongly typed parameters ensure data integrity, and how to use them to pass values both to and from the stored procedure. This tutorial has been a primer in a way for the next tutorial, in which I'll show you how to make use of object oriented programming techniques to contain all this fuzzy ADO code into a simplified data access object. I highly recommend playing with stored procedures, because next time, you're going to want to convert! The data access class we create will cut your development time drastically, because there's no more bothering with Server.CreateObject and so on. Enjoy!

Page 10 of 10