Conversion failed when converting the varchar value ''N'' to data type int.
-
Hi,
I am trying to execute the following stored proc
SET
Ansi_Nulls ON
SET Quoted_IdentIfier ON
GO
ALTER
PROCEDURE [dbo].[sp_Eon_Flags]
@SrvAccsId DECIMAL(12,0),
@DomEonPct DECIMAL(18,4),
@IntEonPct DECIMAL(18,4),
@ZipPct DECIMAL(18,4),
@DomNetInc DECIMAL(18,2),
@AllNetInc DECIMAL(18,2),
@NpFlag VARCHAR(1) OUTPUT,
@DomEonInd VARCHAR(1) OUTPUT,
@IntEonInd VARCHAR(1) OUTPUT,
@ZipInd VARCHAR(1) OUTPUT,
@NetIncInd VARCHAR(1) OUTPUT,
@AllIncInd VARCHAR(1) OUTPUT,
@TrtmntCd VARCHAR(5) OUTPUT
AS
DECLARE @CurrTrtmntCd VARCHAR(5)
DECLARE @OpIncme DECIMAL(18,4)
DECLARE @EonUsage DECIMAL(18,2)
DECLARE @ZipCovrg DECIMAL(18,2)
DECLARE @TrtmntSeq INT
SELECT @OpIncme = mkt_biz_Rules.Operational_IncMe,
@EonUsage = mkt_biz_Rules.OffNet_Usage,
@ZipCovrg = mkt_biz_Rules.Zipcd_covrg
FROM mkt_biz_Rules,
Subs_mst
WHERE mkt_biz_Rules.mkt_cd = Subs_mst.mkt_cd
AND Subs_mst.srv_accs_Id = @SrvAccsId
IF @IntEonPct >= @EonUsage
BEGIN
SET @IntEonInd = 'Y'
END
ELSE
BEGIN
SET @IntEonInd = 'N'
END
IF @DomEonPct >= @EonUsage
BEGIN
SET @DomEonInd = 'Y'
END
ELSE
BEGIN
SET @DomEonInd = 'N'
END
IF @ZipPct <= @ZipCovrg
BEGIN
SET @ZipInd = 'Y'
END
ELSE
BEGIN
SET @ZipInd = 'N'
END
IF @DomNetInc <= @OpIncme
BEGIN
SET @NetIncInd = 'Y'
END
ELSE
BEGIN
SET @NetIncInd = 'N'
END
IF @AllNetInc <= @OpIncme
BEGIN
SET @AllIncInd = 'Y'
END
ELSE
BEGIN
SET @AllIncInd = 'N'
END
IF @NetIncInd = 'Y'
AND (@DomEonInd = 'Y'
OR @ZipInd = 'Y')
BEGIN
SET @NpFlag = 'Y'
END
ELSE
BEGIN
SET @NpFlag = 'N'
END
SELECT @CurrTrtmntCd = Isnull(Subs_mst.Curr_trtmnt_cd,'NOVAL')
FROM Subs_mst
WHERE Subs_mst.srv_accs_Id = @SrvAccsId
IF @CurrTrtmntCd = 'NOVAL'
BEGIN
SELECT @TrtmntCd = trtmnt_cd
FROM mkt_Eon_prgrsn_lkp
WHERE trtmnt_seq = 1
END
ELSE
BEGIN
SELECT @TrtmntSeq = trtmnt_seq + 1
FROM mkt_Eon_prgrsn_lkp,
Subs_mst
WHERE Subs_mst.srv_accs_Id = @SrvAccsId
AND mkt_Eon_prgrsn_lkp.trtmnt_cd = Isnull(Subs_mst.Curr_trtmnt_cd,'A')
AND mkt_Eon_prgrsn_lkp.mkt_cd = Subs_mst.mkt_cd
BEGIN TRY
SELECT @TrtmntCd = trtmnt_cd
FROM mkt_Eon_prgrsn_lkp
WHERE trtmnt_seq = @TrtmntSeq
END TRY
BEGIN CATCH
SET @TrtmntCd = @CurrTrtmntCd
END CATCH
END
Return
@NpFlag
Return
@DomEonInd
Return
@IntEonInd
Return
@ZipInd
Return
@NetIncInd
Return
@AllIncInd
Return
@TrtmntCd
However on execution I am getting the following error -
Msg 245, Level 16, State 1, Procedure sp_eon_flags, Line 121
Conversion failed when converting the varchar value 'N' to data type int.
Could someone please let me know what the problem is?
Thanks,
Uday.
-
Hi,
You are using the return statement to return the value for the OUTPUT varaible @NpFlag... which is creating the problem as return statement only accept the Numeric values.
RETURN SATEMENT is used to exit from the stored procedure and return 0 indicate the SP successfuly completed otherwise the error number.
Cheers
cheers
-
It worked
thanks a ton....uday
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply