写了一个存储过程报错，从数据类型 varchar 转换为 bigint 时出错。

[zy13665886]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	 <Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[FC_UsersInforCount]
 (
 @id bigint,  --会员ID
 @template smallint, --商情状态
 @ut smallint,  --会员类型
 @ht smallint   --房型
 )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
    -- Insert statements for procedure here
 declare @sql1 nvarchar(800)
 declare @sql2 nvarchar(800)
 declare @sql3 nvarchar(800)
 declare @sql4 nvarchar(800)
 declare @sql5 nvarchar(800)
 declare @sql6 nvarchar(800)
if @ht=1
begin
Set @sql1 = 'Select Count(1) from [FC_oHouse] where Users_ID =' + @id + ''
Set @sql2 = 'select Count(1) from [FC_SHouse] where Users_ID = ' + @id + ''
Set @sql3 = 'select Count(1) from [FC_FHouse] where Users_ID = ' + @id + ''
Set @sql4 = 'select Count(1) from [FC_Request] where Req_Userid = ' + @id + ''
Set @sql5 = 'select Count(1) from [FC_RequestSer] where Req_Userid = ' + @id + ''
Set @sql6 = 'Select Count(1) from [FC_Case] where Users_ID = ' + @id + ''
End
-----------------------------------------------------------------------------
if @ut=1        --普通会员
begin
Set @sql1 = @sql1 + ' and sellh_isagent = 0'
Set @sql2 = @sql2 + ' and shoph_isagent = 0'
Set @sql3 = @sql3 + ' and fh_isagent = 0'
Set @sql4 = @sql4 + ' and Req_IsGuest = 0 and Not EXISTS( Select Req_IsGuest Null))'
Set @sql5 = @sql5 + ' and Req_IsGuest = 0 and Not EXISTS( Select Req_IsGuest Null))'	
End
else if @ut=2   --代理会员
begin
Set @sql1 = @sql1 + ' and sellh_isagent = 1'
Set @sql2 = @sql2 + ' and shoph_isagent = 1'
Set @sql3 = @sql3 + ' and fh_isagent = 1'
Set @sql4 = @sql4 + ' and Req_IsGuest = 1 '
Set @sql5 = @sql5 + ' and Req_IsGuest = 1 '
end
else            --其他
begin
Set @sql1 = @sql1
Set @sql2 = @sql2
Set @sql3 = @sql3
Set @sql4 = @sql4
Set @sql5 = @sql5
Set @sql6 = @sql6
end
-----------------------------------------------------------------------------
if @template=1   --全部
begin
Set @sql1 = @sql1
Set @sql2 = @sql2
Set @sql3 = @sql3
Set @sql4 = @sql4
Set @sql5 = @sql5
Set @sql6 = @sql6
end
else if @template=2   --锁住
begin
Set @sql1 = @sql1 + ' and SellH_IsLock = 0'
Set @sql2 = @sql2 + ' and ShopH_IsLock = 0'
Set @sql3 = @sql3 + ' and FH_IsLock = 0'
Set @sql4 = @sql4 + ' and Req_IsLock = 0'
Set @sql5 = @sql5 + ' and Req_IsLock = 0'
Set @sql6 = @sql6 + ' and Case_Islock = 0'
end
else if @template=3  --解开
begin
Set @sql1 = @sql1 + ' and SellH_IsLock = 1'
Set @sql2 = @sql2 + ' and ShopH_IsLock = 1'
Set @sql3 = @sql3 + ' and FH_IsLock = 1'
Set @sql4 = @sql4 + ' and Req_IsLock = 1'
Set @sql5 = @sql5 + ' and Req_IsLock = 1'
Set @sql6 = @sql6 + ' and Case_Islock = 1'
end
else if @template=4  --成功
begin
Set @sql1 = @sql1 + ' and SellH_IsCase = 1'
Set @sql2 = @sql2 + ' and ShopH_IsCase = 1'
Set @sql3 = @sql3 + ' and FH_IsCase = 1'
Set @sql4 = @sql4 + ' and Req_IsCase = 1'
Set @sql5 = @sql5 + ' and Req_IsCase = 1'
Set @sql6 = @sql6 
end
else if @template=5 --删除
begin
Set @sql1 = @sql1 + ' and SellH_IsDel = 1'
Set @sql2 = @sql2 + ' and ShopH_IsDel = 1'
Set @sql3 = @sql3 + ' and FH_IsDel = 1'
Set @sql4 = @sql4 + ' and Req_IsDel = 1'
Set @sql5 = @sql5 + ' and Req_IsDel = 1'
Set @sql6 = @sql6 + ' and Case_IsDel = 1'
end
else if @template=6  --除开删除
begin
Set @sql1 = @sql1 + ' and SellH_IsDel < 1 and SellH_IsDel > 1'
Set @sql2 = @sql2 + ' and ShopH_IsDel < 1 and ShopH_IsDel > 1'
Set @sql3 = @sql3 + ' and FH_IsDel < 1 and FH_IsDel > 1'
Set @sql4 = @sql4 + ' and Req_IsDel < 1 and Req_IsDel > 1'
Set @sql5 = @sql5 + ' and Req_IsDel < 1 and Req_IsDel > 1'
Set @sql6 = @sql6 + ' and Case_IsDel < 1 and Case_IsDel > 1'
end
else if @template=7  --除开成功和删除
begin
Set @sql1 = @sql1 + ' and SellH_IsDel < 1 and SellH_IsDel > 1 and SellH_IsCase < 1 and SellH_IsCase > 1'
Set @sql2 = @sql2 + ' and ShopH_IsDel < 1 and ShopH_IsDel > 1 and ShopH_IsCase < 1 and ShopH_IsCase > 1'
Set @sql3 = @sql3 + ' and FH_IsDel < 1 and FH_IsDel > 1 and FH_IsCase < 1 and FH_IsCase > 1'
Set @sql4 = @sql4 + ' and Req_IsDel < 1 and Req_IsDel > 1 and Req_IsCase < 1 and Req_IsCase > 1'
Set @sql5 = @sql5 + ' and Req_IsDel < 1 and Req_IsDel > 1 and Req_IsCase < 1 and Req_IsCase > 1'
Set @sql6 = @sql6 + ' and Case_IsDel < 1 and Case_IsDel > 1'
end
else
begin
Set @sql1 = @sql1
Set @sql2 = @sql2
Set @sql3 = @sql3
Set @sql4 = @sql4
Set @sql5 = @sql5
Set @sql6 = @sql6
end
-----------------------------------------------------------------------------
declare @Num1 int
declare @Num2 int
declare @Num3 int
declare @Num4 int
declare @Num5 int
declare @Num6 int
declare @uNum int

exec sp_executesql @sql1
print @Num1
exec sp_executesql @sql2
print @Num2
exec sp_executesql @sql3
print @Num3
exec sp_executesql @sql4
print @Num4
exec sp_executesql @sql5
print @Num5
exec sp_executesql @sql6
print @Num6
-----------------------------------------------------------------------------
if @ut=4  
begin
Set @uNum = @Num6
end
else
begin
Set @uNum = @Num1 + @Num2 + @Num3 + @Num4 + @Num5 + @Num6
end 
-----------------------------------------------------------------------------
return @uNum
print @uNum
END

执行后报错：

Microsoft OLE DB Provider for SQL Server 错误 '80040e07'

从数据类型 varchar 转换为 bigint 时出错。


调用代码：
response.Write conn.Execute("Exec FC_Count "&.Fields("Users_ID")&",1,0,1")(0)

因为今天才学着写的，所以看不明白哪里错误了，还请大家帮忙看看

[zy13665886]

最后在页面上显示的要是数字就可以了

[zy13665886]

今天高手都不在吗？没有人回帖啊

[Vidor]

其它照版改： Set @sql1 = 'Select Count(1) from [FC_oHouse] where Users_ID =' + convert(varchar,@id)

[zy13665886]

引用 3 楼 Vidor 的回复:

其它照版改： Set @sql1 = 'Select Count(1) from [FC_oHouse] where Users_ID =' + convert(varchar,@id)

自己解决了，直接 把变量改为 @id varchar(600), --会员ID 结果是出来了，但是数字统计得全都不对 我想要的目的是把这6张表的值全部加起来，就是最后的值 请问要怎么修改

[zy13665886]

我一段一段的切了测试：应该是下面的出错

exec sp_executesql @sql1
		return @Num1
	exec sp_executesql @sql2
		return @Num2
	exec sp_executesql @sql3
		return @Num3
	exec sp_executesql @sql4
		return @Num4
	exec sp_executesql @sql5
		return @Num5
	exec sp_executesql @sql6
		return @Num6
	-----------------------------------------------------------------------------
	if @ut=4   --服务会员
		begin
			Set @uNum = @Num6
		end
	else
		begin
			Set @uNum = @Num1 + @Num2 + @Num3 + @Num4 + @Num5 + @Num6
		end
	-----------------------------------------------------------------------------
	return @uNum

请问要怎么把6条语句都执行，然后加在一起，输出出来啊