存储过程查询失败
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 orderCashEND
GO
错误：System.Data.SqlClient.SqlException (0x80131904): 在将 nvarchar 值 'COunt(1) from users where (1=1) ' 转换成数据类型 int 时失败。
最佳答案
0
这不报错很明显吗，string类型的值无法转换成int类型，你要确定你传的那个string值能不能转成int。
收获园豆：5
我最后得到的是int类型的
@宿醉123: 你把int转string试试，别把string转成int
@徒然喜欢你: 当前数据COUNT(1) from users where (1=1) 条 订单数量COUNT(1) FROM paymentOrder where OrderState=1 and 条 哥哥这是页面显示的东西
其他回答(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