CODEWITHSUNDEEP
mysqlmysql-workbench
Kapil Singh
Kapil Singh

Reputation: 107

Incorrect query result

I have a query which is not returning correct result:

 SELECT t.GroupName AS GroupName, t.ApplicationName AS ApplicationName, t.UserName
        FROM UserApplication t
        WHERE (@ApplicationName IS NULL OR @ApplicationName = '' OR t.ApplicationName = @ApplicationName) AND
              (@UserName IS NULL OR @UserName = '' OR t.UserName= @UserName );

Table structure:

CREATE TABLE userapplication
            (`ID` INT,
             `ApplicationName` VARCHAR(100),
             `GroupName` VARCHAR(100),
             `UserName` VARCHAR(100))

When I do not pass any value to the parameter then it showing all rows from the table while if pass any value to the parameter @ApplicationName or @UserName it is giving me same result. Please help

Upvotes: 0

Views: 96

Answers (1)

peterm
peterm

Reputation: 92805

If you correctly set values of user variables your query will work just fine

SET @ApplicationName = 'App 1';
SET @UserName = '';

SELECT t.GroupName, 
       t.ApplicationName, 
       t.UserName
  FROM UserApplication t
 WHERE (COALESCE(@ApplicationName, '') = '' 
     OR t.ApplicationName = @ApplicationName) 
   AND (COALESCE(@UserName, '') = '' 
     OR t.UserName= @UserName);

It's a little bit more succinct version of your query

Here is SQLFiddle demo

Upvotes: 1

Related Questions