CODEWITHSUNDEEP
sql-server-2008stored-procedureswhere-clause
CeeVei
CeeVei

Reputation: 95

Conditional WHERE clause

Question is simple - I believe answer may be simple too.

System is SQL Server 2008 R2.

There is a stored procedure sp_WHYWHYWHY which takes 3 parameters - 2 are dates and one is supplier code.

The WHERE clause in the statement looks something like this:

OrderDate BETWEEN @StartDate AND @EndDate
AND SupplierCode = @SupplierCode

The business process has changed - as it always will. So now in the supplier code there is only one supplier (let's say YY)who is now aligned, business-wise, to another supplier (let's say ABC).

Hence the code needs to say when querying for ABC - include YY as well.

Something like

OrderDate BETWEEN @StartDate AND @EndDate
AND SupplierCode = CASE WHEN @SupplierCode = 'ABC' THEN 'YY','ABC' ELSE @SupplierCode END

I know you can't write this like that - but that's just what the end result should be.

How can I do this, really?

Thanks for taking out time to read & answer.

Upvotes: 1

Views: 264

Answers (1)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28698

Don't overthink it! You want the supplier code to match the provided value, or the provided value to be ABC and the stored supplier code to be YY.

Try this

AND ([SupplierCode] = @SupplierCode
OR ([SupplierCode] <> @SupplierCode 
    AND @SupplierCode ='ABC' 
    AND [SupplierCode] = 'YY'))

Upvotes: 2

Related Questions