SQL Server存储过程从刚插入的行获取值 [英] SQL Server Stored Procedure Get a value from a row just inserted

问题描述

我是新来编写存储过程，我试图创建将被用来确定是否在数据库中存在PayPal交易ID的之一，如果不使用过程参数，然后返回一个客户添加到数据库无论是之前已经处理新客户的ID或-1表示事务。

I'm new to writing stored procedures and I'm trying to create a one that will be used to determine if a paypal transaction id exists in the database and if not add a customer to the database using the procedure parameters then return either the new customer's ID or -1 to indicate the transaction was already processed before.

下面是我为存储过程：

CREATE PROCEDURE [dbo].[addCustomerIfNotExist] @txn_id varchar(max), @fName nvarchar(255), @lastName nvarchar(255), @e_mail nvarchar(255), @password nvarchar(100), @customerId int OUTPUT
AS
    BEGIN
    -- if the transaction id has not already been processed
    if (select count(txn_id) from paypal_txns WHERE txn_id=@txn_id) = 0
        BEGIN
            --add txn id to the paypal_txns table
            INSERT INTO paypal_txns VALUES(@txn_id)
            --if the email address not is already in the database
            IF (select count(email) from customers where email = @e_mail) = 0
                BEGIN
                    --generate a new customer account 
                    INSERT INTO customers (pcCust_AgreeTerms,pcCust_Guest,pcCust_Residential,pcCust_AllowReviewEmails,name,lastname,email,password) VALUES(0,0,1,1,@fName,@lastName,@e_mail,@password)
                END
            --get the customer id 
            select @customerId = idcustomer from customers where email = @e_mail
        END
    ELSE
        BEGIN
            SET @customerId = -1
        END
    END
GO

我用一个简单的ASP页来测试这个过程的结果。这里是我的code为我的ASP页面批量：

I'm using a simple ASP page to test the results of this procedure. Here is the bulk of my code for my ASP page:

Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = paypalIpnDbCon
   cmd.CommandText = "addCustomerIfNotExist"
   cmd.CommandType = 4 'adCmdStoredProc

   cmd.Parameters.Append cmd.CreateParameter("txn_id", 200, 1,-1,"abcdefg") 

   cmd.Parameters.Append cmd.CreateParameter("fName", 202, 1,-1,"John") 

   cmd.Parameters.Append cmd.CreateParameter("lastName", 202, 1,-1,"Doe") 

   cmd.Parameters.Append cmd.CreateParameter("e_mail", 202, 1,-1,"[e-mail]") 

   cmd.Parameters.Append cmd.CreateParameter("password", 202, 1,-1,randomPassword) 

   cmd.Parameters.Append cmd.CreateParameter("customerId", 3, 2) 

   Set rs = cmd.Execute

   custId = cmd.Parameters("customerId")
   response.write("<br>Customer ID = " & custId & "<br>")

当我不必在paypal_txns表会显示客户ID =好像ID是空的，空，或不存在事务ID（ABCDEFG）运行ASP页面。我运行ASP页面的第二次显示客户ID = -1因为事务ID是在表中了。

When I run the ASP page without having the transaction ID (abcdefg) in the paypal_txns table it will display "Customer ID = " as if the ID is empty, null, or otherwise does not exist. The second time I run the ASP page it displays "Customer ID = -1" because the transaction ID is in the table now.

我也试着调整了codeA位，看看我能得到一个有效的客户ID后面，我可以得到其他的值大于-1如果我改变逻辑左右，但它仍然只在I显示出来运行ASP第二次。

I've also tried tweaking the code a bit to see if I can get a valid customer ID back and I can get a value other than -1 if I change the logic around but it still only shows up after I run the ASP a second time.

我想一定有我俯瞰我的存储过程code东西...任何帮助，将AP preciated。

I'm thinking there must be something I'm overlooking in my stored procedure code... any help would be appreciated.

谢谢！

推荐答案

我没有工作，与很多传统的ASP
但是，而不是采取在输出参数值，如果你能在Recordset对象使用。

i didn't work much with classic asp but instead of taking value in output parameter, if you can use in recordset object.

if (select count(txn_id) from paypal_txns WHERE txn_id=@txn_id) = 0
    BEGIN
        --add txn id to the paypal_txns table
        INSERT INTO paypal_txns VALUES(@txn_id)
        --if the email address not is already in the database
        IF (select count(email) from customers where email = @e_mail) = 0
            BEGIN
                --generate a new customer account 
                INSERT INTO customers (pcCust_AgreeTerms,pcCust_Guest,pcCust_Residential,pcCust_AllowReviewEmails,name,lastname,email,password) VALUES(0,0,1,1,@fName,@lastName,@e_mail,@password)
            END
        --get the customer id 
        select @customerId = idcustomer from customers where email = @e_mail
        select @customerId as custid
    END
ELSE
    BEGIN
        SET @customerId = -1
        select @customerId as custid
    END
END

您可以通过阅读客户ID

You can read the custid using

recordset.fields（客户ID）

recordset.fields("custid")

希望这有助于你。

这篇关于SQL Server存储过程从刚插入的行获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！