首页新闻找找看学习计划

请问如何把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)

跪求大神帮忙!感激不尽!

临冰听雪丶的主页 临冰听雪丶 | 初学一级 | 园豆:5
提问于:2015-04-20 14:20
< >
分享
最佳答案
0

print @sql2

获得输出的sql,然后在查询分析器里执行输出的sql,看报告什么错误,解决后,也就没问题了。

收获园豆:30
519740105 | 大侠五级 |园豆:5810 | 2015-04-20 14:29

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

临冰听雪丶 | 园豆:5 (初学一级) | 2015-04-20 14:42

@临冰听雪丶: 按照我说的报这个错误?

 

貌似:'select * from ('+@sql+') a'

是OK的,尝试下。

519740105 | 园豆:5810 (大侠五级) | 2015-04-20 14:52

@519740105: 谢谢

临冰听雪丶 | 园豆:5 (初学一级) | 2015-04-20 15:01
其他回答(1)
0

要用SET进行赋值

declare @sql varchar(max) 
declare @sql2 varchar(max)
set @sql = '1'
set @sql2 = '2'
收获园豆:20
dudu | 园豆:41311 (高人七级) | 2015-04-20 14:22

我已经给值了啊 

支持(0) 反对(0) 临冰听雪丶 | 园豆:5 (初学一级) | 2015-04-20 14:23

@临冰听雪丶: 不错,之前弄错了。你写的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)
支持(0) 反对(0) dudu | 园豆:41311 (高人七级) | 2015-04-20 14:37

@dudu: 不行,他就报这个错了

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

支持(0) 反对(0) 临冰听雪丶 | 园豆:5 (初学一级) | 2015-04-20 14:43

@临冰听雪丶: print @sql2

支持(0) 反对(0) dudu | 园豆:41311 (高人七级) | 2015-04-20 14:52

@dudu: 谢谢

支持(0) 反对(0) 临冰听雪丶 | 园豆:5 (初学一级) | 2015-04-20 15:01
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册