将varchar转换为Bigint时出错。 [英] Error converting varchar to Bigint.

问题描述

嗨伙计，



我用一个查询来更新表中的状态它在UAT SERVER中工作正常。

但是当我在肝服务器上使用相同的查询我得到以下错误



将varchar转换为Bigint时出错..



我没有看到类型的任何问题。当我删除StatusId = ...然后我看到任何问题。更新statusId存在问题。但不知道如何解决它。

Hi Folks,

I have used one query to update status in table it is working fine in UAT SERVER.
But when i use the same query on liver server i am getting following error

"Error converting varchar to Bigint.."

I dont see any issue with types.when i remove the StatusId = ... then i dnt see any problem.there is an issue with updating statusId.but not sure how to resolve it.

 @TaskId BIGINT,   
 @Percent TINYINT,  
 @LastUpdatedBy CHAR(10), 
 @LastUpdateDateTime DATETIME
AS  
BEGIN  
	UPDATE TaskList SET   
		PercentageCompleted = @Percent,   
		StatusId = (CASE WHEN @Percent = 100 THEN 3   
						 WHEN @Percent <> 0 THEN 2   
						 WHEN (SELECT COUNT(ID) FROM TaskTimerInfo WHERE TaskId = @TaskId) > 0 THEN 2   
						 WHEN (SELECT COUNT(TaskOwner.Id) FROM TaskOwner WHERE TaskOwner.TaskId = TaskList.Id AND TaskOwner.IsActive = 1) <> 0 THEN 1  
						 ELSE 6   
					END),   
		ActualEndDate = CASE WHEN @Percent = 100 AND ActualEndDate IS NULL THEN @LastUpdateDateTime END,   
		LastUpdatedBy = @LastUpdatedBy,   
		LastUpdateDateTime = @LastUpdateDateTime
	WHERE Id = @TaskId  
END

推荐答案

您可以通过查询结果来调试结果，并了解哪些值可用于更新。



尝试在每个任务下运行以下查询并验证结果。您会发现状态异常导致更新失败。

Well you can debug your result by queries the result and see yourself what values are being qualified for update.

Try running below query for each Task and validate the result. You will find anomaly in status that are causing the update to fail.

SELECT  @Percent [Percent],   
		(CASE WHEN @Percent = 100 THEN 3   
						 WHEN @Percent <> 0 THEN 2   
						 WHEN (SELECT COUNT(ID) FROM TaskTimerInfo WHERE TaskId = @TaskId) > 0 THEN 2   
						 WHEN (SELECT COUNT(TaskOwner.Id) FROM TaskOwner WHERE TaskOwner.TaskId = TaskList.Id AND TaskOwner.IsActive = 1) <> 0 THEN 1  
						 ELSE 6   
					END) [Status],   
		CASE WHEN @Percent = 100 AND ActualEndDate IS NULL THEN @LastUpdateDateTime END [ActualEndDate],   
		@LastUpdatedBy [LastUpdatedBy],   
		@LastUpdateDateTime [LastUpdatedDateTime]
FROM TaskList		
WHERE Id = @TaskId 

这篇关于将varchar转换为Bigint时出错。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！