SQL Server 存储过程中的返回值

Return value in stored procedure SQL Server

提问by user186585

I'm trying to use a SQL Server stored procedure with ASP:

我正在尝试将 SQL Server 存储过程与 ASP 一起使用：

ALTER  PROCEDURE [dbo].[user_insert]
    @firstName NVARCHAR(50) ,
    @lastName NVARCHAR(50) ,
    @cityid INT ,
    @email NVARCHAR(100) ,
    @password NVARCHAR(12) ,
    @Affiliate INT
AS 
    BEGIN
        DECLARE @index1 INT
        SET @index1 = 0                      
        IF ( NOT EXISTS ( SELECT    * FROM      dbo.tbl_users
                          WHERE     user_email = @email )  ) 
            BEGIN                                  
                INSERT  INTO dbo.tbl_users
                        ( user_first_name ,
                          user_last_name ,
                          city_id ,
                          user_email ,
                          user_password ,
                          Affiliate_id
                        )
                VALUES  ( @firstName ,
                          @lastName ,
                          @cityid ,
                          @email ,
                          @password ,
                          @Affiliate
                        )
                SET @index1 = ( SELECT  @@IDENTITY
            END
        RETURN @index1 
    END

and the ASP is

和 ASP 是

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = conn
oCmd.CommandText = "user_insert"
oCmd.CommandType = 4

oCmd.Parameters.Append oCmd.CreateParameter("firstName", 203, 1, 100, "1")
oCmd.Parameters.Append oCmd.CreateParameter("lastName", 203, 1, 100, "2" )
oCmd.Parameters.Append oCmd.CreateParameter("cityid", 3, 1, 2, 1)
oCmd.Parameters.Append oCmd.CreateParameter("email", 200, 1, 100, txtmail)
oCmd.Parameters.Append oCmd.CreateParameter("password", 203, 1, 12, "2" )
oCmd.Parameters.Append oCmd.CreateParameter("Affiliate", 3, 1, 3, 1)
oCmd.Parameters.Append oCmd.CreateParameter("@index1", 3, 2)
Set rs = oCmd.Execute
response.write oCmd.Parameters("@index1").Value &"<br>"
Set oCmd = Nothing

If I run the SP in the SQL Server terminal it's working, but when I use the asp code, if it's a "new user", I don't get any value returning "index1"

如果我在 SQL Server 终端中运行 SP，它就可以工作，但是当我使用 asp 代码时，如果它是“新用户”，我将不会得到任何返回“index1”的值

What can I do?

我能做什么？

采纳答案by RichardTheKiwi

Change to adParamReturnValue as the other answer states.

更改为 adParamReturnValue 作为其他答案状态。

You also need to suppress the first resultset, using SET NOCOUNT ON. Also you should stop using @@IDENTITY, use SCOPE_IDENTITY instead.

您还需要使用SET NOCOUNT ON抑制第一个结果集。此外，您应该停止使用 @@IDENTITY，而使用 SCOPE_IDENTITY。

ALTER  PROCEDURE [dbo].[user_insert]
   @firstName nvarchar(50),
   @lastName nvarchar(50),
   @cityid int,
   @email nvarchar(100),
   @password nvarchar(12),
   @Affiliate int
AS BEGIN
   SET NOCOUNT ON

   IF (not EXISTS (SELECT * FROM dbo.tbl_users WHERE user_email=@email))
   begin                                  
      INSERT INTO dbo.tbl_users(user_first_name, user_last_name, city_id, user_email, user_password, Affiliate_id)          
      VALUES(@firstName, @lastName, @cityid, @email, @password, @Affiliate)

      --set
      RETURN SCOPE_IDENTITY()
   end

   RETURN 0
end

Note:You can even omit the RETURN 0 at the end, since 0 is the default return result.

注意：您甚至可以省略最后的 RETURN 0，因为 0 是默认返回结果。

回答by Oded

Your parameter direction is adParamOutput(2), but should be adParamReturnValue(4):

你的参数方向是adParamOutput（2），但应该是adParamReturnValue（4）：

oCmd.Parameters.Append oCmd.CreateParameter("@index1", 3, 4)

See the ParamDirctionEnumthat CreateParametertakes.

看到ParamDirctionEnum那CreateParameter需要。