April 21, 2010 at 1:45 am
Hi All,
This may seem silly, m not sure is it right to post it here...
but i need some urgent help on this..
My begin tran and end tran are incorrect. the code runs only when i comment them. I am not sure why is it happening.
I am pasting the code here.(BEGIN TRAN and END TRAN are commented):
CREATE PROC sp_TLCOMSCandExpertiseArea (@CandID varchar(50),@ExpertiseArea bigint,@var smallint output)
AS
BEGIN
BEGIN TRY
-- BEGIN TRAN
DECLARE @ResumeID BIGINT
set @ResumeID = 0
SELECT @ResumeID = max(rid) FROM hc_resume_bank WHERE uniqueno = @CandID
IF @ExpertiseArea IN (SELECT functionalareaid FROM hc_resume_functionalarea)--change the param to @educationID if they are passing text
BEGIN
UPDATE hc_resume_functionalarea
SET functionalareaid = @ExpertiseArea, ResumeID = @ResumeID
END
ELSE
BEGIN
INSERT INTO hc_resume_functionalarea(functionalareaid,ResumeID)
VALUES (@ExpertiseArea,@ResumeID)
END
COMMIT TRAN
SELECT @var = CASE WHEN @ExpertiseArea = functionalareaid THEN 1 ELSE 0 END
FROM hc_resume_functionalarea WHERE ResumeID = @ResumeID
-- END TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
April 21, 2010 at 1:52 am
END TRAN is not needed, it's not even a valid command.
Transactions begin with BEGIN TRAN and are terminated by a COMMIT or a ROLLBACK command.
Get rid of END TRAN and everything should be ok.
-- Gianluca Sartori
April 21, 2010 at 1:57 am
Thanks Gianluca,
I am new to this SQL Server procs and was really unable to find out the error. Thanks a lot!
April 21, 2010 at 3:03 am
Hi All,
Now that the SP has no error, i have a small doubt regarding its execution.
I have a @var parameter which is a output parameter.
it should return 0 or 1 as per the tran's success or failure.
but i am not getting it.
my exec statement is like this:
exec sp_TLCOMSCandExpertiseArea @CandID = 45,@ExpertiseArea = 11,@var = 0 output
is it ok?
(I have many similar SPs so if the fields dont match no prob, i just want to know how to get @var)
Thanks in advance....
April 21, 2010 at 3:15 am
Try not setting @var value:
exec sp_TLCOMSCandExpertiseArea @CandID = 45,@ExpertiseArea = 11,@var output
-- Gianluca Sartori
April 21, 2010 at 4:15 am
Hi,
I tried but it is asking me to decalre @var !
it is coming from proc, y shud i decalre again?
April 21, 2010 at 6:02 am
To use output values you have to set up the call with an existing variable:
DECLARE @var smallint
exec sp_TLCOMSCandExpertiseArea @CandID = 45,@ExpertiseArea = 11,@var output
SELECT @var
-- Gianluca Sartori
April 21, 2010 at 6:14 am
Thanks again!
I now understood. thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply