开发者

Dealing with conditionally-executed SELECT statements

开发者 https://www.devze.com 2023-03-18 01:12 出处:网络
i have this sp: using sql server 2008 create procedure SelectTopCounts @Id bigint = null, @Count int = null,

i have this sp: using sql server 2008

create procedure SelectTopCounts
    @Id bigint = null,
    @Count int = null,
    @GetAll bit = 0
as
begin
    set nocount on

    IF (@Count IS NULL)
        SELECT @Count = 15 --default

    if(@GetAll = 1) 
       begin
           select col,col2... .......
           --very long s开发者_高级运维elect statement...
       end
    if(@Count is not null)
       begin

           select top @count .....
           --very long select statement...
       end 

Is there a way I can have only ONE select statement instead of duplicating within the if and else condition?


Assuming your table will always have <= 2 billion rows, replace all the IFs/ELSEs with this:

SELECT TOP(COALESCE(CASE @GetAll WHEN 1 THEN 2000000000 END, @Count, 15))
  col1, col2
FROM ...
-- I assume the @Id comes into play somehow...
-- WHERE ID_column = COALESCE(@Id, ID_column);
0

精彩评论

暂无评论...
验证码 换一张
取 消