首页新闻找找看学习计划

存储过程查询失败

0
悬赏园豆:5 [已解决问题] 解决于 2019-03-21 17:31

ALTER PROCEDURE Extend_AppUserCount
@WhrerFirst nvarchar(100)
AS
BEGIN
DECLARE @UserCount INT, @OrderCount INT, @OrderCash decimal
set @UserCount= 'COunt(1) from users where '+ @WhrerFirst+''
set @OrderCash= 'sum(Cash) FROM paymentOrder where OrderState=1 and UserGroupId in(select RelationUserGroupId from CRM_JiHui where RelationUserGroupId in(select GroupId from Users where '+@WhrerFirst+'))'
set @OrderCount= 'COUNT(1) FROM paymentOrder where OrderState=1 and UserGroupId in(select RelationUserGroupId from CRM_JiHui where RelationUserGroupId in(select GroupId from Users where '+@WhrerFirst+'))'

SELECT @UserCount as userCount , @OrderCount as orderCount, @OrderCash as orderCash

END
GO

错误:System.Data.SqlClient.SqlException (0x80131904): 在将 nvarchar 值 'COunt(1) from users where (1=1) ' 转换成数据类型 int 时失败。

耶夫托拉斯基的主页 耶夫托拉斯基 | 菜鸟二级 | 园豆:206
提问于:2018-11-17 16:47
< >
分享
最佳答案
0

这不报错很明显吗,string类型的值无法转换成int类型,你要确定你传的那个string值能不能转成int。

收获园豆:5
徒然喜欢你 | 小虾三级 |园豆:1599 | 2018-11-19 08:52

我最后得到的是int类型的

耶夫托拉斯基 | 园豆:206 (菜鸟二级) | 2018-11-19 09:08

@宿醉123: 你把int转string试试,别把string转成int

徒然喜欢你 | 园豆:1599 (小虾三级) | 2018-11-19 09:21

@徒然喜欢你: 当前数据COUNT(1) from users where (1=1) 条 订单数量COUNT(1) FROM paymentOrder where OrderState=1 and 条 哥哥这是页面显示的东西

耶夫托拉斯基 | 园豆:206 (菜鸟二级) | 2018-11-19 09:29
其他回答(1)
0

@WhrerFirst nvarchar(100)
AS
BEGIN
DECLARE @TempTotal NVARCHAR(4000) ,@TempTotalorder NVARCHAR(4000) ,@TempTotalcash NVARCHAR(4000) ,@UserCount INT ,@OrderCount int ,@OrderCash int
SET @TempTotal='select @UserCount=count(1) FROM users with(nolock) WHERE '+@WhrerFirst
SET @TempTotalorder='select @OrderCount= SUM(Cash) FROM paymentOrder where OrderState=1 and UserGroupId in(select RelationUserGroupId from CRM_JiHui where RelationUserGroupId in(select GroupId from Users where '+@WhrerFirst+'))'
SET @TempTotalcash=' select @OrderCash= COUNT(1) FROM paymentOrder where OrderState=1 and UserGroupId in(select RelationUserGroupId from CRM_JiHui where RelationUserGroupId in(select GroupId from Users where '+@WhrerFirst+'))'
EXECUTE sp_executesql @TempTotal,
N'@UserCount int OUTPUT',
@UserCount OUTPUT
EXECUTE sp_executesql @TempTotalorder,
N'@OrderCount int OUTPUT',
@OrderCount OUTPUT
EXECUTE sp_executesql @TempTotalcash,
N'@OrderCash int OUTPUT',
@OrderCash OUTPUT
SELECT @UserCount AS userCount,@OrderCount as orderCount,@OrderCash as orderCash
END
GO

耶夫托拉斯基 | 园豆:206 (菜鸟二级) | 2018-11-19 13:50
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册