sql - condition in where clause with optional parameter -
there thee input parameter in stored procedure. example,
declare @fromdate datetime = null declare @todate datetime = null declare @id int = null
i want write condition in clause like...if fromdate provided searching must done on @fromdate. if @fromdate not provided check @id variable if not null search basis on @id...
something like...
case when @fromdate not null (@fromdate null or ([created] between @fromdate , @todate)) else (@requestid null or id=@requestid)
there 1 problem below solution...if @fromdate , @id both provided intesect of them , nothing return.....condition should like...if @fromdate given priority gives @fromdate if @id provided , result must not dependend @id parameter....
because depend on both parameters can use them both in condition:
where (@fromdate null or ([created] between @fromdate , @todate)) or ((@requestid null or id=@requestid) , @fromdate null) ----mix @requestid & @fromdate
Comments
Post a Comment