Return value in stored procedure SQL Server

Question

I'm trying to use a SQL Server stored procedure with 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

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"

What can I do?

Solution 1

Change to adParamReturnValue as the other answer states.

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

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.

Solution 2

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

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

See the ParamDirctionEnum that CreateParameter takes.

Comments

[user186585]

try that. still returning null

[Oded]

@user186585 - Try dropping the @ from the parameter name.

[user186585]

it's like the return value is in a new recoredset for some reason

[Oded]

@user186585 - You do need to get the first recordset in the result. See this example: support.microsoft.com/kb/194792

[user186585]

the problem is that in one situation there is one recoredset, and in the other situation there is 2 recordsets. how can i merge this?