asp 中調用存貯過程的一些例子.(2)

來源:互聯網
上載者:User
過程 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_myStoredProcedure
AS
......
Go




This 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
  @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 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, 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 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, 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 at how we would accomplish this same task using a stored procedure. First let's write the procedure.

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



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。