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