现在2段sql代码,我想都优化为一段代码(为了不写重复的代码),或者是一个函数,代码如下:
declare @a int,@b int
if @a = 1
select * from Table1 where b = @b
else if @a = 2
select * from Table2 where b = @bdeclare @a int,@b int ,@c int
if @a = 1
select * from Table1 where b = @b
else if @a = 2
select * from Table1 where c = @c
else if @a = 3
select * from Table1 where a = @a and b = @b and c = @c
请问如何才能优化为一段代码(为了不写重复的代码)呢?
declare @a int,@b int
if @a = 1
select * from Table1 where b = @b
else if @a = 2
select * from Table2 where b = @bdeclare @a int,@b int ,@c int
if @a = 1
select * from Table1 where b = @b
else if @a = 2
select * from Table1 where c = @c
else if @a = 3
select * from Table1 where a = @a and b = @b and c = @c
请问如何才能优化为一段代码(为了不写重复的代码)呢?
重复写代码会好一些, 优化器可以直观知道你想干嘛, 并据此选择出比较优化的执行计划
这就类似于通过层层封装后的 Framework 越来越好用, 但越来越慢
--第1个用动态sql就可以了
declare @a int,@b int
declare @sql varchar(100)
set @sql=isnull(@sql,'')+'select * from Table'+cast(@a as varchar(1))+' where b='''+@b+''''
exec(@sql)第二个要整成一条sql语句不容易
if @a = 1
select * from Table1 where b = @b
else if @a = 2
begin
select * from Table1 where c = @c
select * from Table2 where b = @b
end
else if @a = 3
select * from Table1 where a = @a and b = @b and c = @c 不是代码看起来少才好。