CODEWITHSUNDEEP
sql-serverconditional-statementswhere-clause
RollerCosta
RollerCosta

Reputation: 5186

sql server-conditional where clause

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.

Upvotes: 0

Views: 731

Answers (2)

Andrew Savinykh
Andrew Savinykh

Reputation: 26270

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.

Upvotes: 0

John Dewey
John Dewey

Reputation: 7093

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

Upvotes: 2

Related Questions