Avatar of Ali Saad
Ali SaadFlag for Kuwait asked on

SQL Server 2005 Optional Parameteres To use it in WHERE conditions

Hi Guys
I am trying to create stored procedure to use it for quering purposes , let take this example to show what i needd exactly
create stored procedure GetJVS
@Jv_id int
, @Jv_BK nvarchar(10)
,@Debit  int
,@Credit int
,EntryDAte smalldatetime
,Updateddate smalldatetime
As
Begin
Select * from jvs WHERE ............
END
I need the to Put the condition after Where as optional and if he didnt supply this is means it will not be in hte where condition
In other word
suppose this query
select * from jvs where jv_id=@jv_id and credit = @credit
so if i specify the a default value for parameter in stored procedure creationto be =NULL  the the stamenet it will be
Select * from jvs where jv_id=@jv_id and credit =9.00  and ,@Debit  int
=NULL  And EntryDAte  = NULL  AndUpdateddate = NULL
.............etc
so this is not i need
what i need is
Select * from jvs where jv_id=@jv_id and credit =9.00  
and forget any parameter the user dont supply
so what i should do to get that
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Ali Saad

8/22/2022 - Mon
Lexie

(AndUpdateddate = @Updateddate  OR @Updateddate IS NULL)
Lexie

Or  Updateddate = ISNULL(Updateddate , @Updateddate)
Or you make the sql query dynamic, but that's not neccesary
ASKER
Ali Saad


Using OR will not solve the problem , logically it will get any record mathc with ANY condition in where statment
Please read my post well
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Lexie

Ok, sorry, I thought you meant that parameters that were NULL shouldn't be taken into consideration in the where clause... Like this

create stored procedure GetJVS
@Debit  int = NULL
@Credit int = NULL
As
Begin
Select * from jvs where
(debit = @debit OR @debit IS NULL) AND
(crebit = @crebit OR @debit IS NULL)
end
 
ASKER CERTIFIED SOLUTION
Ali Saad

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Ali Saad

Hello ANGEL I need your HELP