3

What I have is a bit column NonMileage and based on that bit column i want to make a variable that I can use inside of a where clause.

this is a two part question:

  1. How do you case a variable? The code below does not case the @NoMileageListing
  2. And then I have it setting @MileListingClause as a string, can I just use @MileListingClause like where @MileListingClause?

.

SET @NoMileageListing = (SELECT NonMileage FROM tbldealerships);
SELECT 
    @NoMileageListing CASE @NoMileageListing when 1 then 
        SET @MileListingClause = 'tblcargarage.miles >= 0' else 
        SET @MileListingClause = 'tblcargarage.miles != 0' end case;

here's the answer

SET @NoMileageListing = (SELECT NonMileage FROM tbldealerships);

SELECT CASE @NoMileageListing
          WHEN 1 THEN 'tblcargarage.miles >= 0'
          ELSE 'tblcargarage.miles != 0'
       END
  INTO @NoMileWhereClause;
  select @NoMileWhereClause;

found here: Mysql Storing a variable with the result of an SELECT CASE

Community
  • 1
  • 1
  • What type of `@NoMileageListing`? Is it temporary table? Because `(SELECT NonMileage FROM tbldealerships)` give you result set of `NonMileage`. – bitoshi.n May 18 '12 at 16:38
  • @bitoshi.n I'm not sure what you mean NoMileageListing = 1 in this case, the second line where there is a case is the issue –  May 18 '12 at 17:01
  • I ask you, what type of `@NoMileageListing`? Because on the 1st line, it is such as table. But on the 2nd line, it is scalar variable. It may the problem. – bitoshi.n May 18 '12 at 17:12
  • I still have no clue what you mean, it's not a table, it's a variable from the start. –  May 18 '12 at 17:13

1 Answers1

2

I think the problem is located in the when part. It should be:

SELECT CASE @NoMileageListing
WHEN @NoMileageListing = 1 THEN 'tblcargarage.miles >= 0'
ELSE 'tblcargarage.miles != 0'
END
INTO @NoMileWhereClause;
select @NoMileWhereClause;

to create the dynamic query with the @noMileWhereClause refer to the answer here: How To have Dynamic SQL in MySQL Stored Procedure

Community
  • 1
  • 1
Ruben Verschueren
  • 822
  • 13
  • 28