Home > Net >  Error converting data type varchar to numeric in my SQL Server stored procedure
Error converting data type varchar to numeric in my SQL Server stored procedure

Time:11-05

I wrote this stored procedure and tried to retrieve data. But I get this error:

Msg 8114, Level 16, State 5, Procedure spMobileOperationsNew, Line 0 [Batch Start Line 0]
Error converting data type varchar to numeric.

Can you help me to fix this issue?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spMobileOperationsNew]
    @action AS nvarchar(50),
    @territorycode nvarchar(10) = '',
    @disYear  AS int = '',
    @disMonth AS nvarchar(10) = '',
    @territoryTarget AS decimal(18, 2) = '',
    @terDiscRate AS decimal(5, 2) = '',
    @maxDiscRate AS decimal(5, 2) = '',
    @minBill AS decimal(18, 2) = '',
    @allocateBudget AS decimal(18, 2) = '',
    @actualValue AS decimal(18, 2) = '',
    @actDiscValue AS decimal(18, 2) = '',
    @finalBudget AS decimal(18, 2) = ''
AS
BEGIN
    SET NOCOUNT ON;

    IF (@action = 'Get_TerritoryWiseTradeDiscount')
    BEGIN
        PRINT ('get territory')

        SELECT
            LTRIM(RTRIM(tt.[TerCode])) AS [TerCode], 
            tt.[Year], 
            (CASE tt.[Month] 
                 WHEN 'January' THEN 1 
                 WHEN 'February' THEN 2 
                 WHEN 'March' THEN 3 
                 WHEN 'April' THEN 4 
                 WHEN 'May' THEN 5 
                 WHEN 'June' THEN 6 
                 WHEN 'July' THEN 7 
                 WHEN 'August' THEN 8 
                 WHEN 'September' THEN 9
                 WHEN 'October' THEN 10 
                 WHEN 'November' THEN 11 
                 WHEN 'December' THEN 12 
             END) AS [Month], 
            tt.[MaxDiscRt], tt.[MinBillVal], tt.[TerTgt], 
            tt.[TerDiscRt], tt.[AllDiscBud], tt.[ActVal], 
            tt.[ActDiscVal], tt.[FinalDiscBudget]
        FROM
            TerritoryWiseTradeDiscount AS tt
        WHERE 
            tt.[TerCode] = @territorycode 
            AND tt.[Year] = @disYear 
            AND tt.[Month] = @disMonth
    END
END

When I execute this line

EXEC [dbo].[spMobileOperationsNew]'Get_TerritoryWiseTradeDiscount','KEG',2021,'November'

I get the error shown above.

CodePudding user response:

ALTER PROCEDURE [dbo].[spMobileOperationsNew]
@action as nvarchar(50),
@territorycode nvarchar(10)='',
@disYear  as int = '',---?????? may be 0
@disMonth as nvarchar(10)= '',
@territoryTarget as decimal(18,2) ='',---????? may be 0.00
@terDiscRate as  decimal(5,2) ='',--???? may be 0.00
@maxDiscRate as  decimal(5,2) ='',--may be 0.00
@minBill as  decimal(18,2) ='',--may be 0.00
@allocateBudget as  decimal(18,2) ='',--may be 0.00
@actualValue as  decimal(18,2) ='',--may be 0.00
@actDiscValue  as  decimal(18,2) ='',--may be 0.00
@finalBudget as  decimal(18,2) =''--may be 0.00
  • Related