首页新闻找找看学习计划

SQLsever查询的一个问题

0
[已关闭问题] 关闭于 2019-05-28 11:10
DECLARE @p nvarchar(1000)

set @p=N'select Base_MoneyPayRec.id,UserName=(case when Base_MoneyPayRec.RecType=1 then (select OwerName from Base_House where Id=Base_MoneyPayRec.HId )else(select OwerName from Base_House where Id=(select UserId from Base_Park where Id=Base_MoneyPayRec.HId )) end),FloorName=(case when Base_MoneyPayRec.RecType=1 then ( select FloorName from Base_Building where Id=(select BdId from Base_House where Id=Base_MoneyPayRec.HId)) else (select FloorName from  Base_Building where Id=(select BdId from Base_House where Id=(select UserId from Base_Park where Id=Base_MoneyPayRec.HId)) ) end),UnitNmb=(case when Base_MoneyPayRec.RecType=1 then ( select UnitNmb from Base_House where Id=Base_MoneyPayRec.HId)else (select UnitNmb from Base_House where Id=(select UserId from Base_Park where Id=Base_MoneyPayRec.HId))end),HouseNum=(case when Base_MoneyPayRec.RecType=1 then HouseNum else (select HourseNmb from Base_House where Id=(select UserId from Base_Park where Id=Base_MoneyPayRec.HId)) end), ItemName =(Stuff((select ''、'' + ItemName from Base_MoyItem where Id in(select ItemId  from Base_MoyExpenseRec where '',''+(Case when RIGHT(Base_MoneyPayRec.LinkIds,1)='','' then LEFT (Base_MoneyPayRec.LinkIds,LEN(Base_MoneyPayRec.LinkIds)-1) else Base_MoneyPayRec.LinkIds end)+'','' like ''%,''+convert(varchar, Id)+'',%'' ) for XML Path('''')),1,1,'''')),AddTime,Money,PrefeMoney,ChargeMoney,OffsetMoney,RealMoney,UName,PayType,PayCode,ReMark from Base_MoneyPayRec'


Exec sp_executesql @p

通过以上代码在SQLsever中执行的时候,会报出错误:

然后如果直接执行SQL的话,就没问题

Exec sp_executesql N'select Base_MoneyPayRec.id,UserName=(case when Base_MoneyPayRec.RecType=1 then (select OwerName from Base_House where Id=Base_MoneyPayRec.HId )else(select OwerName from Base_House where Id=(select UserId from Base_Park where Id=Base_MoneyPayRec.HId )) end),FloorName=(case when Base_MoneyPayRec.RecType=1 then ( select FloorName from Base_Building where Id=(select BdId from Base_House where Id=Base_MoneyPayRec.HId)) else (select FloorName from  Base_Building where Id=(select BdId from Base_House where Id=(select UserId from Base_Park where Id=Base_MoneyPayRec.HId)) ) end),UnitNmb=(case when Base_MoneyPayRec.RecType=1 then ( select UnitNmb from Base_House where Id=Base_MoneyPayRec.HId)else (select UnitNmb from Base_House where Id=(select UserId from Base_Park where Id=Base_MoneyPayRec.HId))end),HouseNum=(case when Base_MoneyPayRec.RecType=1 then HouseNum else (select HourseNmb from Base_House where Id=(select UserId from Base_Park where Id=Base_MoneyPayRec.HId)) end), ItemName =(Stuff((select ''、'' + ItemName from Base_MoyItem where Id in(select ItemId  from Base_MoyExpenseRec where '',''+(Case when RIGHT(Base_MoneyPayRec.LinkIds,1)='','' then LEFT (Base_MoneyPayRec.LinkIds,LEN(Base_MoneyPayRec.LinkIds)-1) else Base_MoneyPayRec.LinkIds end)+'','' like ''%,''+convert(varchar, Id)+'',%'' ) for XML Path('''')),1,1,'''')),AddTime,Money,PrefeMoney,ChargeMoney,OffsetMoney,RealMoney,UName,PayType,PayCode,ReMark from Base_MoneyPayRec where 1=1 and GetState=1 and ((HId in (select id from Base_House where  1=1 and cid = 79) and RecType=1) or (HId in (select id from Base_Park where UserId in(select id from Base_House where  1=1 and cid = 79)) and RecType=2))'

请问这是为什么呢?

LiuDanK的主页 LiuDanK | 初学一级 | 园豆:29
提问于:2019-05-28 11:00
< >
分享
所有回答(1)
1

@p变量再定义长点,如:
DECLARE @p nvarchar(3000)

lhdz_bj | 园豆:218 (菜鸟二级) | 2019-10-02 16:42

嗯, 感谢回答,虽然时间很久远了,哈哈

支持(0) 反对(0) LiuDanK | 园豆:29 (初学一级) | 2019-10-02 19:45
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册