Hi friend

I have a stored procedure which accepts 3 arguments that are used in the WHERE clause of a SELECT Statement

CREATE PROCEDURE [dbo].[MyProcedure] @.argYear INT, @.argMonth INT, @.argDay INT
AS

SELECT *

FROM MyData

WHERE
MyData.Year = @.argYear AND MyData.Month = @.argMonth AND MyData.Day = @.argDay

The problem that I am having is @.argDay is an "optional" argument. If @.argDay is NULL then I want to basically ignore the "AND MyData.Day = @.argDay" condition.

Is there an easier way to do this than:

IF @.argDay is NULL

SELECT *

FROM MyData

WHERE

MyData.Year = @.argYear AND MyData.Month = @.argMonth

ELSE

SELECT *

FROM MyData

WHERE

MyData.Year = @.argYear AND MyData.Month = @.argMonth AND MyData.Day = @.argDay

END IF

Thanks

You can OR in a not null condition to handle it; perhaps something like:

Code Snippet

SELECT *
FROM MyData
WHERE MyData.Year = @.argYear
AND MyData.Month = @.argMonth

AND ( MyData.Day = @.argDay or @.argDay is null )

|||

One method is something like this:

WHERE ( MyData.Year = @.argYear

AND MyData.Month = @.argMonth

AND ( MyData.Day = @.argDay

OR MyData.Day IS NULL

)

)

|||

Whatever you are using it is the best method. If you add expression on variable the Index Scan will be forced on your query..

The current query is neat & clean, will give a best performance. Stick it there itself.

|||Thanks for all your help - it works!
|||

I want to learn ASP.Net with c# from the basic stage.If you get some useful links and tutorials,please send me.