title: Invalid object name error in a case statement - SQL Server
body: <p>I have been given an assignment to write a procedure in SQL Server. I have used some queries in a case statement for the procedure's code. Somehow, the SSMS tells that the tables used in the query are invalid. I have tried refreshing the local cache but the effort went in vain. What is the necessary change I have to make?</p>
  
  <p>The following is the erroneous procedure code:</p>
  
  <pre><code>CREATE TABLE cconvert (
      lc_id INT PRIMARY KEY
      ,lc_name VARCHAR(30) NOT NULL UNIQUE
      ,ac_name VARCHAR(30) NOT NULL
      ,cf FLOAT
      )
  
  CREATE TABLE invoice_amount (
      inv_id INT PRIMARY KEY
      ,inv_amt FLOAT NOT NULL
      ,lc_name VARCHAR(30) NOT NULL
      ,ac_name VARCHAR(30) NOT NULL
      ,conv_inv_amt FLOAT
      )
  
  INSERT INTO cconvert
  VALUES (
      1
      ,'USD'
      ,'USD'
      ,1
      )
  
  INSERT INTO cconvert
  VALUES (
      2
      ,'British Pound'
      ,'USD'
      ,0.637989
      )
  
  INSERT INTO cconvert
  VALUES (
      3
      ,'Indian Rupee'
      ,'USD'
      ,63.511192
      )
  
  INSERT INTO cconvert
  VALUES (
      4
      ,'Australian Dollar'
      ,'USD'
      ,1.264736
      )
  
  INSERT INTO cconvert
  VALUES (
      5
      ,'Canadian Dollar'
      ,'USD'
      ,1.218956
      )
  
  INSERT INTO cconvert
  VALUES (
      6
      ,'Singapore Dollar'
      ,'USD'
      ,1.330278
      )
  
  INSERT INTO cconvert
  VALUES (
      7
      ,'Swiss Franc'
      ,'USD'
      ,0.933432
      )
  
  INSERT INTO cconvert
  VALUES (
      8
      ,'Malaysian Ringgit'
      ,'USD'
      ,3.584753
      )
  
  INSERT INTO cconvert
  VALUES (
      9
      ,'Japanese Yen'
      ,'USD'
      ,120.762671
      )
  
  INSERT INTO cconvert
  VALUES (
      10
      ,'Chinese Yuan Renminbi'
      ,'USD'
      ,6.199559
      )
  
  SELECT *
  FROM cconvert
  
  SELECT *
  FROM invoice_amount
  
  INSERT INTO invoice_amount
  VALUES (
      1
      ,16880.16000
      ,'British Pound'
      ,'USD'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      2
      ,30035.19000
      ,'Indian Rupee'
      ,'USD'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      3
      ,2550.71000
      ,'Australian Dollar'
      ,'USD'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      4
      ,21.33000
      ,'Canadian Dollar'
      ,'USD'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      5
      ,38560.78000
      ,'Singapore Dollar'
      ,'USD'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      6
      ,38560.78000
      ,'Swiss Franc'
      ,'Swiss Franc'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      7
      ,98.84000
      ,'Malaysian Ringgit'
      ,'Malaysian Ringgit'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      8
      ,30583.38000
      ,'Japanese Yen'
      ,'USD'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      9
      ,3468.9000
      ,'Chinese Yuan Renminbi'
      ,'USD'
      ,NULL
      )
  
  INSERT INTO invoice_amount
  VALUES (
      10
      ,16300.55000
      ,'USD'
      ,'USD'
      ,NULL
      )
  
  DELETE
  FROM cconvert
  
  DROP TABLE cconvert
  GO
  
  CREATE PROCEDURE show_invoice_details
  AS
  BEGIN
      DECLARE @table_size INT
          ,@i INT
          ,@lc_name VARCHAR(30)
          ,@ac_name VARCHAR(30)
          ,@conv_inv_amt FLOAT
          ,@cf FLOAT
          ,@inv_amt FLOAT
  
      DECLARE inv_id_cursor CURSOR
      FOR
      SELECT *
      FROM invoice_amount
      ORDER BY inv_id
  
      OPEN inv_id_cursor
  
      FETCH inv_id_cursor
      INTO @i
          ,@inv_amt
          ,@lc_name
          ,@ac_name
          ,@conv_inv_amt
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          IF @conv_inv_amt IS NOT NULL
          BEGIN
              FETCH inv_id_cursor
              INTO @i
                  ,@inv_amt
                  ,@lc_name
                  ,@ac_name
                  ,@conv_inv_amt
  
              CONTINUE
          END
          ELSE
          BEGIN
              IF @lc_name = @ac_name
                  SET @cf = 1
              ELSE
              BEGIN
                  SET @cf = CASE 
                          WHEN @lc_name = 'British Pound'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'Indian Rupee'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'Australian Dollar'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'Canadian Dollar'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'Singapore Dollar'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'Swiss Franc'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'Malaysian Ringgit'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'Japanese Yen'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'Chinese Yuan Renminbi'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          WHEN @lc_name = 'USD'
                              THEN (
                                      SELECT cf
                                      FROM cconvert
                                      WHERE lc_name = @lc_name
                                      )
                          END
                  SET @conv_inv_amt = @inv_amt * @cf
  
                  UPDATE invoice_amount
                  SET conv_inv_amt = @conv_inv_amt
              END
          END
  
          CLOSE inv_id_cursor
  
          DEALLOCATE inv_id_cursor
  
          SELECT *
          FROM invoice_amount
      END
          GO
  
      EXEC show_invoice_details
  
      DROP show_invoice_details GO
  </code></pre>