title: Return value in stored procedure SQL Server
body: <p>I'm trying to use a SQL Server stored procedure with ASP:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and the ASP is</p>
  
  <pre><code>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 &amp;"&lt;br&gt;"
  Set oCmd = Nothing
  </code></pre>
  
  <p>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"</p>
  
  <p>What can I do?</p>