存储过程SQL Server中的返回值

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

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","2" )
oCmd.Parameters.Append oCmd.CreateParameter("cityid",3,2,1)
oCmd.Parameters.Append oCmd.CreateParameter("email",200,txtmail)
oCmd.Parameters.Append oCmd.CreateParameter("password",12,"2" )
oCmd.Parameters.Append oCmd.CreateParameter("Affiliate",1)
oCmd.Parameters.Append oCmd.CreateParameter("@index1",2)
Set rs = oCmd.Execute
response.write oCmd.Parameters("@index1").Value &"<br>"
Set oCmd = Nothing

如果我在SQL Server终端中运行SP它正在工作,但是当我使用asp代码时,如果它是“新用户”,我没有得到任何返回“index1”的值

我能做什么？

解决方法

您还需要使用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,Affiliate_id)          
      VALUES(@firstName,@Affiliate)

      --set
      RETURN SCOPE_IDENTITY()
   end

   RETURN 0
end

注意：您甚至可以省略末尾的RETURN 0,因为0是默认的返回结果.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!