Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
ALTER PROCEDURE [dbo].[SP_GetAmountByGLCode]  
  
-- EXEC SP_GetAmountByGLCode '','',50,'','','1501102000'  
  
   @FromDate nvarchar(50),  
   @ToDate nvarchar(50),  
   @PaidStatus int,  
   @UlbCode varchar(10),  
   @filenumber varchar(100),  
   @GLCODE varchar(100)  
AS  
SET NOCOUNT ON;  
BEGIN  
    
--SET FMTONLY OFF;   
  
    declare @strSql nvarchar(max)  
  
    set @strSql = 'select ud.UlbCode as PlaceName, rf.GlCode, CAST(Convert(decimal(18,2), SUM(id.Amount)) as varchar(50)) as [TotalAmount]   
 from RequestFee rf, InvoiceDetail id, Invoice i, UlbData ud, PlanRequest pr, ScrutinyRequest sr, Place p, Fee f  
 where i.PlanRequestId = pr.PlanRequestId and rf.PlanRequestId = pr.PlanRequestId and   
 pr.PlanRequestId = sr.PlanRequestId and sr.ParentUlbId = ud.PlaceId and p.PlaceId = sr.ParentUlbId and p.PlaceId = ud.PlaceId and  
 i.InvoiceId = id.InvoiceId and id.RequestFeeId = rf.RequestFeeId and f.FeeId = rf.FeeId and f.IsDeleted = ''N''   
 and i.IsDeleted = ''N'' and rf.GLCODE != 1401201012 '   
   
 IF (@FromDate = '' OR @FromDate is null)  
 BEGIN  
     set @FromDate = '05-16-2018'  
     print @FromDate  
 END  
   
 IF (@ToDate = '' OR @ToDate is null)  
 BEGIN  
     set @ToDate = CAST(GETDATE() as nvarchar(50))  
     print @ToDate  
 END  
 --  ' + convert(int, @UlbCode) + ''  
 set @strSql = @strSql + 'and CONVERT(date, i.PaidDate) >= cast(''' + @FromDate + ''' as date) ' +  
                         'and CONVERT(date, i.PaidDate) <= cast(''' + @ToDate + ''' as date) ' +  
                         'and i.PaidStatusId = ' + convert(varchar(10), @paidstatus)  
 PRINT @UlbCode  
 IF (@UlbCode <> '' OR @UlbCode <> null)  
 BEGIN  
  set @strSql = @strSql + ' and ud.UlbCode = cast(''' + @UlbCode + ''' as int) '  
    
 END  
     
 IF (@GLCODE <> '' OR @GLCODE <> null)  
 BEGIN  
  set @strSql = @strSql + ' and rf.GlCode = ''' + @GLCODE + ''''  
    
 END  
  
 IF (@filenumber <> '' OR @filenumber <> null)  
 BEGIN  
  set @strSql = @strSql + ' and sr.ScrNo = ''' + @filenumber + ''''   
 END  
                           
 set @strSql = @strSql + ' group by ud.UlbCode,rf.GlCode order by ud.UlbCode,rf.GlCode'   
   
 --return exec sp_Executesql @strSql  
  
 declare @t table (PlaceName varchar(50), GlCode varchar(50), TotalAmount varchar(50) );  
 insert into @t exec sp_Executesql @strSql;  
  
 select PlaceName as [ULB],GlCode as [GLCode],TotalAmount as [Amount]  from @t;  
  
  
 END  


What I have tried:

how to resolve,i try all the declaration to bigint and long from int
Posted
Updated 23-Aug-20 21:43pm

1 solution

If you try to cast '3502033000' as an INT it will always fail - it's 1GB too big o fit.
Casting it as a BIGINT will work though:
SQL
SELECT '3502033000', CAST('3502033000' as BIGINT)
works fine for me.
So there are two things to try:
1) Find everywhere you are CASTing or CONVERTing things to INT, or using a NVARCHAR field or variable as an INT - and there are a few of them in the SP - and work out which one is causing the problem, and make that a BIGINT.
2) Stop using string data fields to store numeric values so you don't need to convert them at all.

Me? I'd go with the latter - it has the additional advantages that the data is always correct (no alpha data in a numeric field) and the SQL becomes a whole load more readable.
 
Share this answer
 
Comments
Garth J Lancaster 24-Aug-20 3:48am    
"Me? I'd go with the latter" ...yup I used to work with a DBA who would fail code reviews for 'un-necessary CASTing'
OriginalGriff 24-Aug-20 3:59am    
It's just laziness - it's easier to create a db of all strings, and pass strings all the time form the presentation software.
Then when you actually need to use it for a sort, or maths, ... it all collapses because you didn't validate the strings before storing a bad date.
Then you're looking at weeks of work to manually rationalise the whole production DB ...
Garth J Lancaster 24-Aug-20 4:34am    
yup - laziness - same as C# 'var' (ducks and runs for cover)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900