April 21, 2010 at 4:50 am
Hi All,
I have a proc with parameters @CandID,@Status,@var
@var is an output parameter and its value will be 1 if the proc inserts a row and 0 if it doesnt.
So , after the procedure is executed, @var should be shown with either 0 or 1.
in code i have written a case to achieve that.
now i am not getting how to exec this.
my EXEC statement is asking me to declare @var.
Please help.
the stmnt is like :
EXEC Sp_myproc @candID = 12,@status = 1 @var output
April 21, 2010 at 5:10 am
hi,
Declare @var int
EXEC Sp_myproc @candID = 12,@status = 1 ,@var=@var output
SELECT@var as N'@var'
April 21, 2010 at 5:25 am
Hi,
Thanks for the reply. but this is giving error. I think my SP has some prob.
Could you pls chk that and let me know?
ALTER 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 TRY
BEGIN CATCH
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
Thanks in adv.
April 21, 2010 at 5:28 am
Sorry it was a MINOR ERROR. I have corrected it.
Thanks for the code 🙂
April 21, 2010 at 5:28 am
Sorry it was a MINOR ERROR. I have corrected it.
Thanks for the code 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply