Process writing a Stored Procedure part II
by Nathan Pond
--------------------------------------------------------------------------------
This article is a continuation the my previous article, writing a Stored Procedure
Let me start out by the correcting (or rather updating) something I said in my-I-article. I said there that I wasn ' t aware of the a way to update a stored the procedure without it and deleting it. Now I am. :-) There is a ALTER comand can use as this:
ALTER PROCEDURE Sp_mystoredprocedure
As
......
Go
This would overwrite the stored procedure that being there with the new set of commands, but'll keep permissions, so it is Better than dropping and recreating the procedure. Many to Pedro Vera-perez for e-mailing me with this info.
As promised I am going to dive into the more detail about stored procedures. Let me start off by answering a common question I received via e-mail. Many people wrote asking if it is possible, and if so I to does it, to use stored procedures does to more than select state ments. Absolutely!!! Anything can accomplish in a SQL statement can is accomplished in a stored procedure, simply because a stored pro Cedure can execute SQL statements. Let's look in a simple INSERT example.
CREATE PROCEDURE Sp_myinsert
@FirstName varchar (20),
@LastName varchar (30)
As
INSERT into Names (FirstName, LastName)
VALUES (@FirstName, @LastName)
Go
Now, call this procedure with the parameters and it'll insert a new row into the Names table with the FirstName and last Name columns approiately assigned. And is a example of how to call this procedure with parameters from ASP page:
<%
Dim DataConn, sSQL
Dim FirstName, LastName
FirstName = "Nathan"
LastName = "Pond"
Set DataConn = Server.CreateObject ("ADODB. Connection ")
Dataconn.open "Dsn=webdata;uid=user;pwd=password" ' Make connection
sSQL = "Sp_myinsert '" & FirstName & "', '" & LastName & ""
Dataconn.execute (sSQL) ' Execute SQL call
%>
Remeber, you can use the stored procedures for anything, including UPDATE and DELETE calls. Just embed a SQL statement into the procedure. Notice that's above procedure doesn ' t return anything, so don ' t need to set a recordset. The same is true for UPDATE and DELETE calls. The only statement that returns a recordset is the SELECT statement.
Now, the just because a recordset isn ' t returned, it doesn ' t mean that there ' t being 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 your need to look at the Datab ASE, if they match, then you allow of the user to logon, otherwise your redirect them to a incorrect logon page. Without a stored procedures you would does something like this:
<%
Dim DataConn, sSQL, RS
Set DataConn = Server.CreateObject ("ADODB. Connection ")
Dataconn.open "Dsn=webdata;uid=user;pwd=password" ' Make connection
sSQL = "SELECT * from user_table Where UserName = '" & _
Request.Form ("UserName") & "' and Password = '" & _
Request.Form ("Password") & "'"
Set rs = dataconn.execute (ssql) ' Execute SQL call
If Rs. EOF Then
' Redirect user, incorrect login
Response.Redirect "Incorrect.htm"
End If
' Process logon code
.............
%>
Now let's look in how we would accomplish the this same task using a stored procedure. The procedure of the ' s ' write.
CREATE PROCEDURE Sp_isvalidlogon
@UserName varchar (16),
@Password varchar (16)
As
if exists (Select * from user_table
Where UserName = @UserName
and
Password = @Password)
Return (1)
Else
