[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

stored procedure / asp.net

For some reasons, when IF EXISTS(SELECT MemberId FROM Members Where Email = @Email And StatusId = 1) is matched.

@ReturnMemberId always return nothing.
It suppose to return "0"

Any experts can find out what wrong with the code?
The insert side is working perfectly fine.

Thanks

ALTER PROCEDURE dbo.AddMember
      (
      @Email VARCHAR(255),
      @Passcode VARCHAR(255),
      @FirstName VARCHAR(255),
      @LastName VARCHAR(255),
      @MiddleName VARCHAR(255),
      @MemberTypeId INT,
      @StatusId INT,
      @CreatedDate DATETIME,
      @ReturnMemberId INT OUTPUT
      )
        
AS
DECLARE @TEST INT
    SET @TEST = 0
      IF EXISTS(SELECT MemberId FROM Members Where Email = @Email And StatusId = 1)
      BEGIN
      SELECT @ReturnMemberId=@TEST
      END
      ELSE
      INSERT INTO Members
      (
      Email, Passcode, FirstName, LastName, MiddleName, MemberTypeId,
      StatusId, CreatedDate)
      VALUES      
      (
      @Email,@Passcode,@FirstName,@LastName,@MiddleName,@MemberTypeId,
      @StatusId,@CreatedDate
      )
      BEGIN
      SELECT @ReturnMemberId = @@IDENTITY
      END
      
      RETURN
0
Webboy2008
Asked:
Webboy2008
  • 2
1 Solution
 
sachitjainCommented:
Error is apparent, your insert statement is following the ELSE then BEGIN is following Insert. Ideally Begin should be before insert to scope both insert and select identity statement within BEGIN-END block of ELSE condition as per my understanding of your logic

ALTER PROCEDURE dbo.AddMember
      (
      @Email VARCHAR(255),
      @Passcode VARCHAR(255),
      @FirstName VARCHAR(255),
      @LastName VARCHAR(255),
      @MiddleName VARCHAR(255),
      @MemberTypeId INT,
      @StatusId INT,
      @CreatedDate DATETIME,
      @ReturnMemberId INT OUTPUT
      )
       
AS
DECLARE @TEST INT
SET @TEST = 0
IF EXISTS(SELECT MemberId FROM Members Where Email = @Email And StatusId = 1)
BEGIN
      SELECT @ReturnMemberId=@TEST
END
ELSE
Begin
      INSERT INTO Members
      (
            Email, Passcode, FirstName, LastName, MiddleName, MemberTypeId,
            StatusId, CreatedDate)
      VALUES      
      (
            @Email,@Passcode,@FirstName,@LastName,@MiddleName,@MemberTypeId,
            @StatusId,@CreatedDate
      )
      SELECT @ReturnMemberId = @@IDENTITY
END

RETURN
0
 
EyalCommented:
SELECT @TEST = MemberId FROM Members Where Email = @Email And StatusId = 1
      IF (NOT @TEST IS NULL and @TEST>0)
      BEGIN
      SELECT @ReturnMemberId=@TEST
      END
...
0
 
Webboy2008Author Commented:
sachitjain: it does not seem any different to me.
0
 
sachitjainCommented:
No with the little change in logic I did, either @ReturnMemberId would be 0 or set to value of identity column based on your IF condition. You could actually try this to see the difference.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now