无法将char值转换为money。 char值的语法不正确。

问题描述

大家好我不明白我收到以下错误

Hi all I did not understand it i'm getting the following error

**"Warning: Null value is eliminated by an aggregate or other SET operation.
(3 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 235, Level 16, State 0, Procedure ProfitandLossReportSP, Line 23
Cannot convert a char value to money. The char value has incorrect syntax.
"**

可以任何正文请帮我解决这个问题.....

Can any body Please help me to solve this.....

ALTER Procedure [dbo].[ProfitandLossReportSP](@FromDate date,@ToDate date,@FromDateChange date,@ToDateChange date)
as
begin
Declare @Amount money
Declare @Count int
Declare @AccountType nvarchar
Declare @AccountNo nvarchar
Declare @AccountName nvarchar
/*Fetching Values From ProfitandLossView for Specific DateRange */
Declare ListOfAmount cursor for 
select Account_Type,Account_No,Account_Name,SUM(Amount) from dbo.ProfitandLossView 
where  Dated between @FromDateChange and @ToDateChange group by Account_Type,Account_No,Account_Name
/* Temp Table declaration*/
Declare @Temp table (Account_Type nvarchar(100),Account_No nvarchar(100),Account_Name nvarchar(100) ,Amount money, Previous_Period money,Amount_Change money,Percentage_Change money,Previous_Year money,Previous_Year_Amount_Change money,Previous_Year_Percentage_Change money)
/* Inserting set of Values into Temp Table for different DateRange*/
insert into @Temp(Account_Type,Account_No,Account_Name,Amount)  
Select Account_Type,Account_No,Account_Name,SUM(Amount) from dbo.ProfitandLossView 
where  Dated >= @FromDate and Dated < DATEADD(dd, 1, @ToDate)  group by Account_Type,Account_No,Account_Name
/* Loops starts */
/* Updating Temp table Amount2 Column from ListOfAmount */
open ListOfAmount
Fetch next from ListOfAmount into @Amount,@AccountType,@AccountNo,@AccountName
while @@Fetch_Status=0
begin
	set @Count=(select count(*) from @Temp where Account_Name=@AccountName)
	if(@Count>0)
	begin
		update @Temp set Previous_Period = @Amount where  Account_Name=@AccountName
		Fetch next from ListOfAmount into @Amount,@AccountType,@AccountNo,@AccountName
	end
	else
	begin
		insert into @Temp(Account_Type,Account_No,Account_Name,Previous_Period) values (@AccountType,@AccountNo,@AccountName,@Amount)
	end
End
/* Closing and Deallocating List */
Close ListOfAmount
Deallocate ListOfAmount

/* Variable Declaration for Money Change Calculation */
Declare @AmountChange1 money
Declare @AmountChange2 money
Declare @AccountTypeChange varchar(100)
Declare @AccountNoChange varchar(100)
Declare @AccountNameChange varchar(100)

/* Fetching Values from Temp table into List  */
Declare ListOfAmountChange cursor for 
select Amount,Previous_Period,Account_Type,Account_No,Account_Name from @Temp 
/*Loop starts*/
/* Calculating Value Change and updating Temp table*/
open ListOfAmountChange
Fetch next from ListOfAmountChange into  @AmountChange1,@AmountChange2,@AccountTypeChange,@AccountNoChange,@AccountNameChange
while @@Fetch_Status=0
begin
if(@AmountChange1 is null)
	begin
		update @Temp set Amount_Change=@AmountChange2, Percentage_Change='100.00%' where Account_No = @AccountNo		
	end
	else if (@AmountChange2 is null)
	begin
	 update @Temp set Amount_Change=@AmountChange1 ,Percentage_Change='100.00%' where Account_No = @AccountNo
	end
	else if (@AmountChange1 is not null and @AmountChange2 is not null)
	begin
	 update @Temp set Amount_Change=@AmountChange1-@AmountChange2 , Percentage_Change=(((@AmountChange1-@AmountChange2) /@AmountChange2)*100)+'%' where Account_No = @AccountNo	
	end
	 else
	 begin
	 update @Temp set Amount_Change=null ,Percentage_Change='0.00%' where Account_No = @AccountNo	
	  end
	  Fetch next from ListOfAmountChange into @AmountChange1,@AmountChange2,@AccountTypeChange,@AccountNoChange,@AccountNameChange
	  	end
/* Variable declaration*/
Declare @LastYearAmount money
Declare @LastYearCount int
Declare @LastYearAccountType varchar(100)
Declare @LastYearAccountNo varchar(100)
Declare @LastYearAccountName varchar(100)
declare @previousfrom date
declare @previousyearto date

set @previousfrom=DateAdd(yy, -1, @FromDate)
set @previousyearto=DateAdd(yy, -1, @ToDate)
/*Fetching Values From ProfitandLossView for LastYear DateRange */
Declare ListOfLastYearAmount cursor for 
select Account_Type,Account_No,Account_Name,SUM(Amount) from dbo.ProfitandLossView 
where  Dated between @previousfrom and @previousyearto group by Account_Type,Account_No,Account_Name
/* Inserting set of Values into Temp Table for Last Year DateRange*/
/* Loops starts */
/* Updating Temp table LastYearAmount Column from ListOfLastYearAmount */
open ListOfLastYearAmount
Fetch next from ListOfLastYearAmount into @LastYearAmount,@LastYearAccountType,@LastYearAccountNo,@LastYearAccountName
while @@Fetch_Status=0
begin
set @LastYearCount=(select count(*) from @Temp where Account_No=@LastYearAccountNo)
	if(@LastYearCount>0)
	begin
		update @Temp set Previous_Year = @LastYearAmount where Account_No=@LastYearAccountNo
		Fetch next from ListOfLastYearAmount into @LastYearAmount,@LastYearAccountType,@LastYearAccountNo,@LastYearAccountName
	end
	else
	begin
		
		insert into @Temp(Account_Type,Account_No,Account_Name, Previous_Year) values (@LastYearAccountType,@LastYearAccountNo,@LastYearAccountName,@LastYearAmount)
	end
End
/* Closing and Deallocating List */
Close ListOfLastYearAmount
Deallocate ListOfLastYearAmount

/* Variable Declaration for Last Year Money Change Calculation */
Declare @LastYearAmountChange1 money
Declare @LastYearAmountChange2 money
Declare @LastYearAccountTypeChange varchar(100)
Declare @LastYearAccountNoChange varchar(100)
Declare @LastYearAccountNameChange varchar(100)
/* Fetching Values from Temp table into List  */
Declare ListOfLastYearAmountChange cursor for 
select Previous_Period,Previous_Year,Account_Type,Account_No,Account_Name from @Temp 
/*Loop starts*/
/* Calculating Last Year Value Change and updating Temp table*/
open ListOfLastYearAmountChange
Fetch next from ListOfLastYearAmountChange into  @LastYearAmountChange1,@LastYearAmountChange2,@LastYearAccountTypeChange,@LastYearAccountNoChange,@LastYearAccountNameChange
while @@Fetch_Status=0
begin
	if(@LastYearAmountChange1 is null)
	begin
		update @Temp set Previous_Year_Amount_Change=@LastYearAmountChange2, Previous_Year_Percentage_Change='100.00%' where Account_No = @LastYearAccountNoChange		
	end
	else if (@LastYearAmountChange2 is null)
	begin
	 update @Temp set Previous_Year_Amount_Change=@LastYearAmountChange1 ,Previous_Year_Percentage_Change='100.00%' where Account_No = @LastYearAccountNoChange 
	end
	else if (@LastYearAmountChange1 is not null and @LastYearAmountChange2 is not null)
	begin
	 update @Temp set Previous_Year_Amount_Change=@LastYearAmountChange1-@LastYearAmountChange2 , Previous_Year_Percentage_Change=(((@LastYearAmountChange1-@LastYearAmountChange2) /@LastYearAmountChange2)*100 )+'%' where Account_No = @LastYearAccountNoChange	
	end
	 else
	 begin
	 update @Temp set Previous_Year_Amount_Change=null ,Previous_Year_Percentage_Change='0.00%' where Account_No = @LastYearAccountNoChange		
	  end
	Fetch next from ListOfLastYearAmountChange into @LastYearAmountChange1,@LastYearAmountChange2,@LastYearAmountChange1,@LastYearAmountChange2,@LastYearAccountTypeChange,@LastYearAccountNoChange,@LastYearAccountNameChange	
	end
/*Select all the Values from Temp table */
select * from @Temp

end



GO

推荐答案

这篇关于无法将char值转换为money。 char值的语法不正确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！