请问如何把sql做为declare来使用？
0
悬赏园豆：50
[已解决问题]
解决于 2015-04-20 15:02
假如说我有这样的一个sql需求，
select top 1 a.ExamTypeName, a.MoneyTable_ID,a.MName,a.MMonry, b.MoneyTable_ID,b.MName as newNames,b.MMonry as newMoney, c.MoneyTable_ID,c.MName as Name3,c.MMonry as Money3 from (select * from TB_ExamTypes as et inner join TB_MoneyTable as mt on mt.categoryID = et.ExamType_ID) as a left join (select * from TB_ExamTypes as et inner join TB_MoneyTable as mt on mt.categoryID = et.ExamType_ID) as b on a.ExamType_ID = b.ExamType_ID and a.MName != b.MName left join (select * from TB_ExamTypes as et inner join TB_MoneyTable as mt on mt.categoryID = et.ExamType_ID) as c on a.ExamType_ID = c.ExamType_ID and a.MName != c.MName and b.MName != c.MName go
但是，我想要把公共的部分只写一遍，请问有什么办法啊？
我这样写但是不对
declare @sql varchar(max) = '1' declare @sql2 varchar(max) = '2' set @sql = 'select * from CostRelation as cr inner join CostItem ci on ci.CostID = cr.CostID' set @sql2 = 'select * from (select '+@sql+')' exec (@sql2)
跪求大神帮忙！感激不尽！
最佳答案
0
print @sql2
获得输出的sql，然后在查询分析器里执行输出的sql，看报告什么错误，解决后，也就没问题了。
收获园豆：30
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
@临冰听雪丶: 按照我说的报这个错误？
貌似：'select * from ('+@sql+') a'
是OK的，尝试下。
@519740105: 谢谢
其他回答(1)
0
要用SET进行赋值
declare @sql varchar(max) declare @sql2 varchar(max) set @sql = '1' set @sql2 = '2'
收获园豆：20
我已经给值了啊
@临冰听雪丶: 不错，之前弄错了。你写的SQL中多了一个SELECT，改为这样：
declare @sql varchar(max) = '1' declare @sql2 varchar(max) = '2' set @sql = 'select * from CostRelation as cr inner join CostItem ci on ci.CostID = cr.CostID' set @sql2 = 'select * from ('+@sql+')' exec (@sql2)
@dudu: 不行，他就报这个错了
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
@临冰听雪丶: print @sql2
@dudu: 谢谢
