sql server-conditional where clause

Question

Suggest me the proper syntex for below raw condition.

ALTER PROCEDURE [dbo].[pro_name]
@number as int ,

Select * from table 
if(@number=0)
begin
set @number=select max(number)from table
end
where table.number=@number

here i need to set @number with max value if input value passed is '0'. and also want to use th e same in wherer clause.

the WHERE clause - do you want @number to be used before or after calculating MAX ? — YS.
– YS., Commented Mar 9, 2012 at 5:05
where table.number= case number when 0 then MAX(table.number) else number (NOT WORKING ) — RollerCosta
– RollerCosta, Commented Mar 9, 2012 at 5:17
@YS. i want where clause with conditional value i.e if else before where clause. in any case use number but with diff value (according to the condition) — RollerCosta
– RollerCosta, Commented Mar 9, 2012 at 5:19

John Dewey · Accepted Answer · 2012-03-09 05:10:01Z

2

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 t.number=@number;
go

edited Mar 9, 2012 at 5:10

answered Mar 9, 2012 at 5:04

John Dewey

7,1234 gold badges25 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew Savinykh · Accepted Answer · 2012-03-09 05:24:11Z

0

EDIT:

Since there is not point in having two exactly the same answers and I was beaten, here is an alternate solution:

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

However this will only work if the number column is unique, which may or may not be the case in your case.

edited Mar 9, 2012 at 5:24

answered Mar 9, 2012 at 5:05

Andrew Savinykh

26.7k20 gold badges110 silver badges168 bronze badges

Collectives™ on Stack Overflow

sql server-conditional where clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related