Webboy2008
asked on
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,@FirstNam e,@LastNam e,@MiddleN ame,@Membe rTypeId,
@StatusId,@CreatedDate
)
BEGIN
SELECT @ReturnMemberId = @@IDENTITY
END
RETURN
@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,@FirstNam
@StatusId,@CreatedDate
)
BEGIN
SELECT @ReturnMemberId = @@IDENTITY
END
RETURN
SELECT @TEST = MemberId FROM Members Where Email = @Email And StatusId = 1
IF (NOT @TEST IS NULL and @TEST>0)
BEGIN
SELECT @ReturnMemberId=@TEST
END
...
IF (NOT @TEST IS NULL and @TEST>0)
BEGIN
SELECT @ReturnMemberId=@TEST
END
...
ASKER
sachitjain: it does not seem any different to me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,@FirstNam
@StatusId,@CreatedDate
)
SELECT @ReturnMemberId = @@IDENTITY
END
RETURN