CODEWITHSUNDEEP
sqlsql-server-2008
lemunk
lemunk

Reputation: 2636

SQL query not returning data from where clause

using MS SQL server 2008.

I have a query as follows:

SELECT * FROM #PreModuleAllData WITH(NOLOCK)
WHERE (@Country   != 'DEFAULT' AND @Country   != '' AND([Language] = @Country ))
OR    (@UserType  != 'DEFAULT' AND @UserType  != '' AND([UserType] = @UserType ))
OR    (@Group     != 'DEFAULT' AND @Group     != '' AND([Company] = @Group ))
OR    (@CodeUsage != 'DEFAULT' AND @CodeUsage != '' AND([User Code]= @CodeUsage ))

The plan was to return all data if any of the parameters where set to '' or default. if a paramter was used it should return based on that particular where clause.

For example if the parameter @country is set to a language i.e english the query returns data.

But if all params are set to default nothing is returned, but i need all records to return, I have a feeling the logic is missing something very simple but my eyes cant see why.

Any clues?

Upvotes: 1

Views: 663

Answers (3)

Surendra
Surendra

Reputation: 721

you have to re-write the logic as below 

SELECT * FROM #PreModuleAllData WITH(NOLOCK)
WHERE (@Country   = 'DEFAULT' OR @Country   = '' OR (@Country NOT IN ('DEFAULT','') AND [Language] = @Country ))
OR    (@UserType  = 'DEFAULT' OR @UserType  = '' OR (@UserType NOT IN ('DEFAULT','') AND [UserType] = @UserType ))
OR    (@Group     = 'DEFAULT' OR @Group     = '' OR (@Group NOT IN ('DEFAULT','') AND [Company] = @Group ))
OR    (@CodeUsage = 'DEFAULT' OR @CodeUsage = '' OR (@CodeUsage NOT IN ('DEFAULT','') AND [User Code]= @CodeUsage ))

Upvotes: 0

whastupduck
whastupduck

Reputation: 1166

You need a boolean condition in your where clause:

WHERE ((@Country = 'DEFAULT') OR @Country!= 'DEFAULT' AND @Country != '' AND([Language] = @Country ))
AND

if the parameter country is not default, it will apply that condition. otherwise if the paremeter is default then the second condition will be skipped.

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Try this:

SELECT * FROM #PreModuleAllData WITH(NOLOCK)
WHERE (@Country   = 'DEFAULT' OR @Country   = '' OR ([Language] = @Country ))
AND   (@UserType  = 'DEFAULT' OR @UserType  = '' OR ([UserType] = @UserType ))
AND   (@Group     = 'DEFAULT' OR @Group     = '' OR ([Company] = @Group ))
AND    (@CodeUsage = 'DEFAULT' OR @CodeUsage = '' OR ([User Code]= @CodeUsage ))

Upvotes: 4

Related Questions