sql server-conditional where子句
问题描述:
建议我在以下原始状态下使用正确的syntex。
sql server-conditional where子句
ALTER PROCEDURE [dbo].[pro_name]
@number as int ,
Select * from table
if(@number=0)
begin
set @number=select max(number)from table
end
where [email protected]
这里我需要设置@number与最大值如果通过输入值为“0”。并且还希望在wherer子句中使用同样的内容。
答
ALTER PROCEDURE [dbo].[pro_name]
@number as int=0 --I recommend a default value here too
as
set @number=case @number when 0 then (select max(number)from [table]) else @number end;
Select * from [table] t
where [email protected];
go
答
编辑:
由于没有指向有两个完全一样的答案,我被打,这里是一个替代的解决方案:
alter procedure [dbo].[pro_name]
@number as int as
select top 1 * from [table]
where number = @number or @number = 0
order by [table].number desc
然而,这只会如果数字列是唯一的,那么可能会或可能不会如此。
'@ number'使用案例 – joshua 2012-03-09 04:53:26
'WHERE'子句 - 您希望在计算MAX之前还是之后使用'@ number'? – 2012-03-09 05:05:32
where table.number = case number when 0 then MAX(table.number) else number(NOT WORKING) – RollerCosta 2012-03-09 05:17:38