asp 中调用存贮过程的一些例子.(2)

writing a stored procedure part ii by nathan pond --------------------------------------------------------------------------------this article is a continuation of my previous article, writing a stored procedure let me start out by first correcting (or rather updating) something i said in my first article. i said there that i wasn't aware of a way to update a stored procedure without deleting it and recreating it. well now i am. :-) there is an alter comand you can use, like this: alter procedure sp_mystoredprocedureas......gothis will overwrite the stored procedure that was there with the new set of commands, but will keep permissions, so it is better than dropping and recreating the procedure. many thanks to pedro vera-perez for e-mailing me with this info. as promised i am going to dive into more detail about stored procedures. let me start out by answering a common question i received via e-mail. many people wrote asking if it was possible, and if so how to do it, to use stored procedures do to more than select statements. absolutely!!! anything that you can accomplish in a sql statement can be accomplished in a stored procedure, simply because a stored procedure can execute sql statements. let's look at a simple insert example. create procedure sp_myinsert@firstnamevarchar(20),@lastname varchar(30)asinsert into names(firstname, lastname)values(@firstname, @lastname)gonow, call this procedure with the parameters and it will insert a new row into the names table with the firstname and lastname columns approiately assigned. and here is an example of how to call this procedure with parameters from an asp page: <%dim dataconn, ssqldim firstname, lastnamefirstname = "nathan"lastname = "pond"set dataconn = server.createobject("adodb.connection")dataconn.open"dsn=webdata;uid=user;pwd=password" 'make connectionssql = "sp_myinsert '" & firstname & "', '" & lastname & "'"dataconn.execute(ssql) 'execute sql call%>remeber, you can use stored procedures for anything, including update and delete calls. just embed a sql statement into the procedure. notice that the above procedure doesn't return anything, so you don't need to set a recordset. the same will be true for update and delete calls. the only statement that returns a recordset is the select statement. now, just because a recordset isn't returned, it doesn't mean that there won't be a return value. stored procedures have the ability to return single values, not just recordsets. let me show you a practical example of this. suppose you have a login on your site, the user enters a username and password, and you need to look these up in the database, if they match, then you allow the user to logon, otherwise you redirect them to an incorrect logon page. without a stored procedures you would do something like this: <%dim dataconn, ssql, rsset dataconn = server.createobject("adodb.connection")dataconn.open"dsn=webdata;uid=user;pwd=password" 'make connectionssql = "select * from user_table where username = '" & _request.form("username") & "' and password = '" & _request.form("password") & "'"set rs = dataconn.execute(ssql) 'execute sql callif rs.eof then'redirect user, incorrect loginresponse.redirect "incorrect.htm"end if'process logon code.............%>now let's look at how we would accomplish this same task using a stored procedure. first let's write the procedure. create procedure sp_isvalidlogon@usernamevarchar(16),@passwordvarchar(16)asif exists(select * from user_tablewhere username = @username andpassword = @password)return(1)elsereturn(0)gowhat this procedure does is take the username and password as input parameters and performs the lookup. if a record is returned the stored procedure will return a single value of 1, if not the procedure will return 0. no recordset is returned. let's look at the asp you would use: <%<!--#include virtual="/include/adovbs.inc"-->dim dataconn, adocmd, isvalidset dataconn = server.createobject("adodb.connection")dataconn.open"dsn=webdata;uid=user;pwd=password" 'make connectionset adocmd = server.createobject("adodb.command")adocmd.commandtext = "sp_isvalidlogon"adocmd.activeconnection = dataconnadocmd.commandtype = adcmdstoredprocadocmd.parameters.append adocmd.createparameter("return", _ adinteger, adparamreturnvalue, 4)adocmd.parameters.append adocmd.createparameter("username", _ advarchar, adparaminput, 16, _ request.form("username"))adocmd.parameters.append adocmd.createparameter("password", _ advarchar, adparaminput, 16, _ request.form("password"))adocmd.executeisvalid = adocmd.parameters("return").valueif isvalid = 0 then'redirect user, incorrect loginresponse.redirect "incorrect.htm"end if'process logon code.............%>in part 2 we'll look at the ado command object, and how you can use it to execute stored procedures through your asp pages. we'll also look at why you should use stored procedures as opposed to dynamic queries. read part 2 writing a stored procedure part ii, part 2 by nathan pond --------------------------------------------------------------------------------read part 1 in part 1, i introduced a lot of new things, so lets slow down for a minute and i'll go through them. first thing i did was create a command object for ado. i did this with: set adocmd = server.createobject("adodb.command")next i had to tell the object what command it would be executing, with this line: adocmd.commandtext = "sp_isvalidlogon"notice that the command is the name of the stored procedure. you must tell the command object which connection (database) to use, to do this you use .activeconnection. .commandtype is a property that tells sql what type of command it is trying to execute. adcmdstoredproc is a constant variable declared in the include file adovbs.inc. (for more information on adovbs.inc, be sure to read adovbs.inc - use it!) it represents the number telling sql that the command is to execute a stored procedure. the .append method is used to add return values and parameters. i had to add the username and password parameters, as well as set up the return value. i then executed the command with .execute, and .parameters("return").value held the return value from the procedure. i set that to the variable isvalid. if isvalid is 0, the login is incorrect, if it is 1, the login is correct. now even after the explanation this is still a lot to take in. my recommendation to you is to dive into your server and try a few simple tasks like this. practice makes perfect. one note: sometimes i get errors when i try to .append the return value after i have already set the parameters. meaning i might get an error if the above code looked like this: <%.....set adocmd = server.createobject("adodb.command")adocmd.commandtext = "sp_isvalidlogon"adocmd.activeconnection = dataconnadocmd.commandtype = adcmdstoredprocadocmd.parameters.append adocmd.createparameter("username", _advarchar, adparaminput, 16, request.form("username"))adocmd.parameters.append .createparameter("password", _advarchar, adparaminput, 16, request.form("password"))adocmd.parameters.append .createparameter("return", _adinteger, adparamreturnvalue, 4)adocmd.executeisvalid = adocmd.parameters("return").value.....%>i'm not exactly sure why this happens, but i just made it a habit to declare the return value first, then the parameters. now i know what some of you are saying. "the original asp example for checking the username and password without using a stored procedure is so much easier, all you did was confuse me! can stored procedures actually be used to improve efficiency?" well i'm glad you asked, because although the example above did require a bit more code, it is important to realize that it is much more efficient. stored procedures have other benefits besides efficiency, though. for a full explanation of the benefits of stored procedures, be sure to read the sql guru's advice on the issue. and now i am going to show you an example of a task where using stored procedures minimizes your database calls. assume you have the same script as before for validating usernames and passwords. all it really does is say whether it is a valid username and password. suppose you want to add functionality in to log all failed attempts at logging on into another table called failedlogons. if you weren't using a stored procedure you would have to make another call to the database from your asp code. however, in the example using the stored procedure, we don't have to touch the asp code at all, we simply modify the procedure like so: alter procedure sp_isvalidlogon@usernamevarchar(16),@passwordvarchar(16)asif exists(select * from user_table where username = @usernameandpassword = @password)beginreturn(1)endelsebegininsert into failedlogons(username, password)values(@username, @password)return(0)endgowasn't that neat? but that's not all, while we're at it why not add a little more functionality? let's say that we want to run a check on each incorrect login, and if there have been more than 5 incorrect logins for that username within the past day, that account will be disabled. we would have to have the failedlogons table set up to have a dtfailed column with a default value of (getdate()). so when the incorrect logon is inserted into the table, the date and time is recorded automatically. then we would modify our stored procedure like this: alter procedure sp_isvalidlogon@usernamevarchar(16),@passwordvarchar(16)asif exists(select * from user_tablewhere username = @usernameandpassword = @password)beginreturn(1)endelsebegininsert into failedlogons(username, password)values(@username, @password)declare @totalfailsintselect @totalfails = count(*) from failedlogonswhere username = @usernameand dtfailed > getdate()-1if (@totalfails > 5)update user_table set active = 0where username = @usernamereturn(0)endgonow, let's take a closer look at what i was doing. first thing, check to see if the username and password exist on the same row, if they do, login is fine, return 1 to the user and exit the procedure. if the login is not ok though, we want to log it. the first thing the procedure does is insert the record into the failedlogons table. next we declare a variable to hold the number of failed logons for that same day. next we assign that value by using a sql statement to retrieve the number of records for that username, within the same day. if that number is greater than 5, it's likely someone is trying to hack that account so the the username will be disabled by setting the active flag in the user_table to 0. finally, return 0 letting the calling code (asp) know that the login was unsuccessful. to accomplish this same task using only asp, you would have needed to make 4 database calls. the way we just did it it is still only one database call, plus the fact that all that functionality we added at the end was in the stored procedure, we didn't have to touch the asp code at all! note about begin/end: when using an if statement in a stored procedure, as long as you keep the conditional code to one line you won't need a begin or end statement. example: if (@myvar=1)return(1)elsereturn(2)however, if you need more than one line, it is required that you use begin and end. example: if (@myvar=1)begindo this.....and this.....return(1)endelsebegindo this....return(2)endi hope that i have given enough information to keep you active in learning stored procedures. if you're anything like me, getting the basics is the hard part, from there you can experiment and learn on your own. that is why i decided to create these two articles. remember, feel free to e-mail me at npond@bgnet.bgsu.edu with any questions or comments about either of my articles. and thanks to everyone who wrote to me regarding part one of this series. happy programing!