April 22, 2010 at 4:20 am
Hi All,
The control is not going to the ELSE block. Please help!
ALTER PROCEDURE [dbo].[sp_TEMPCandReg] (@CandID VARCHAR(50) output,@FirstName VARCHAR(100),@MiddleName Varchar(100),
@LastName VARCHAR(100),@EmailID VARCHAR(250),@MobileNo VARCHAR (15),
@PhoneNo VARCHAR (15),@DOB DATETIME,@GenderSMALLINT,@Address Varchar(250),
@CountryBIGINT,@State BIGINT,@Location BIGINT,@ResumeUpload VARCHAR(100),
@TotalExperience DECIMAL(18,2),@Industry BIGINT,@FunctionalArea BIGINT,@Working SMALLINT,
@WorkingSince DATETIME,@PresentEmployer VARCHAR(100),@PresentCTC DECIMAL(18,2),@ExpectedCTC DECIMAL(18,2),
@PreviousEmployer VARCHAR(100),@CentreIDBIGINT,@CandStatusVARCHAR(20),
@CreatedUserID BIGINT,@LastModifiedUserID BIGINT)--,@InstituteName VARCHAR(100)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
DECLARE @ResumePrefix VARCHAR(50)
DECLARE @ResumeSuffix VARCHAR(50)
DECLARE @ResumeLastNo BIGINT
DECLARE @uniqueno varchar(50)
DECLARE @rid BIGINT
set @rid=0
DECLARE @RetVal SMALLINT
SET @RetVal = 0
PRINT '1'
IF @CandID IS NULL OR @CandID = ' '
BEGIN
PRINT '2'
/* second if*/ IF NOT EXISTS
(SELECT FirstName,LastName,EmailID,Mobile FROM hc_resume_bank
WHERE ((FirstName=@FirstName OR LastName=@FirstName OR MiddleName=@FirstName AND (@FirstName not like '' or @FirstName not like' '))
OR (FirstName=@LastName OR LastName=@LastName OR MiddleName=@LastName AND (@LastName not like '' or @LastName not like ' '))
OR (EmailID =@EmailID AND (@EmailID not like '' or @EmailID not like ' '))
OR (Mobile =@MobileNo AND (@MobileNo not like '' or @MobileNo not like ' ')) )
)
BEGIN
SELECT @uniqueno = ResumePrefix+cast(ResumeLastNo as varchar)+ResumeSuffix FROM HC_SYSCONFIG
PRINT @uniqueno
UPDATE HC_SYSCONFIG SET ResumeLastNo = ResumeLastNo + 1
PRINT '3'
INSERT INTO hc_resume_bank(UniqueNo,FirstName,MiddleName,LastName
,EmailId,Mobile,PhoneH,DOB,Gender,CountryID,StateID,LocationID
,ResumeConvertedText,TotalExp,IndTypeID,FunctionID,Working,WorkingFrom,PresentEmployer,Address1
,PresentCTC,ExpectedCTC,ResumeSourceID,ResumeStatus,CreatedUserID)
-- ,LastModifiedUserID)
VALUES (@uniqueno,@FirstName,@MiddleName,@LastName
,@EmailID,@MobileNo,@PhoneNo,@DOB,@Gender
,@Country,@State,@Location,@ResumeUpload ,@TotalExperience,@Industry,@FunctionalArea
,@Working,@WorkingSince,@PresentEmployer,@Address,@PresentCTC,@ExpectedCTC,@CentreID,@CandStatus,@CreatedUserID--,@LastModifiedUserID
)
SELECT @rid = max(rid) FROM hc_resume_bank where uniqueno = @uniqueno
PRINT @rid
PRINT '4'
INSERT INTO HC_RESUME_EMPLOYER (ResumeID,Employer,Particular)
VALUES (@rid,@PreviousEmployer,1)
-- SELECT * FROM HC_RESUME_EMPLOYER
PRINT '5'
INSERT INTO HC_RESUME_BANK_HISTORY(ResumeID,UserID,SourceType,Type)
VALUES (@rid,@CreatedUserID,1,0)
-- SELECT * FROM HC_RESUME_HISTORY
END
ELSE
BEGIN
PRINT 'Record Exists'
SET @RetVal = -1
PRINT @RetVal
END
/*End of second IF*/ END
/*ELSE Cond for first IF*/
ELSE
BEGIN
PRINT '6'
UPDATE HC_RESUME_BANK
SET FirstName = @FirstName,LastName = @LastName,EmailID = @EmailID,Mobile = @MobileNo,
PhoneH = @PhoneNo, DOB = @DOB,Gender = @Gender,CountryID = @Country,StateID = @State,LocationID = @Location,
ResumeConvertedText = @ResumeUpload,TotalExp = @TotalExperience,IndTypeID = @Industry,FunctionID = @FunctionalArea,
WorkingFrom = @WorkingSince,PresentEmployer = @PresentEmployer,PresentCTC = @PresentCTC,ExpectedCTC = @ExpectedCTC,
ResumeStatus = @CandStatus,LastModifiedUserID = @LastModifiedUserID,DocModifiedDate = getutcdate()
WHERE uniqueno = @CandID
print 'Update Completed'
END
/*End of first IF*/
-- PRINT '8'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'ERROR'
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
IF @@TRANCOUNT > 0
ROLLBACK TRAN
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState )
END CATCH
end
-------------------------------------------------------------------------
And the messages I am getting are :
1
2
Record Exists
-1
(1 row(s) affected)
(1 row(s) affected)
********************************************************
I dont know why is this 1 row(s) affected message is shown.
I have executed for EXISTING record and it should update that reccord.
April 22, 2010 at 10:40 am
Can you send the call you're making to the procedure? It looks like you're not passing in something for @CandID but we'll need to see the call and how any variables are populated to make sure.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply