0

I want this procedure change the table name when I execute it.

The table name that I want to change is Recargas_@mes

There is some way to do that?

@MES DATETIME

AS
BEGIN 

   SELECT CUENTA, SUM(COSTO_REC) COSTO_REC 
   INTO E09040_DEV.BI_PRO_COSTO_RECARGAS 
   FROM (                        
      SELECT a.*,(CASE
      WHEN COD_AJUSTE IN ('ELEC_TEXT','TFREPPVV_C') THEN (A.VALOR)*(R.COSTO) ELSE 0 END)
      FROM Recargas_@MES AS A,  BI_PRO_LISTA_COSTOS_RECARGAS AS R
      WHERE R.ANO_MES =  @MES
   ) D  
   GROUP BY CUENTA

END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Taco585
  • 5
  • 2
  • Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Mar 15 '16 at 22:06
  • Possible duplicate of [Table name as variable](http://stackoverflow.com/questions/2838490/table-name-as-variable) – Ken White Mar 15 '16 at 22:06

2 Answers2

1

Sample code:

-- Declare variables
DECLARE @MES DATETIME;
DECLARE @TSQL NVARCHAR(MAX);

-- Set the variable to valid statement
SET @TSQL = N'
   SELECT CUENTA, SUM(COSTO_REC) AS COSTO_REC 
   INTO E09040_DEV.BI_PRO_COSTO_RECARGAS 
   FROM (                        
      SELECT A.*,
          (CASE
            WHEN COD_AJUSTE IN (''ELEC_TEXT'',''TFREPPVV_C'') THEN 
              (A.VALOR)*(R.COSTO) 
            ELSE 0 
           END)
      FROM 
          Recargas_' + REPLACE(CONVERT(CHAR(10), @MES, 101), '/', '') + ' AS A,  
          BI_PRO_LISTA_COSTOS_RECARGAS AS R
      WHERE R.ANO_MES = ' +  CONVERT(CHAR(10), @MES, 101) + '
   ) D  
   GROUP BY CUENTA'

-- Execute the statement 
EXECUTE (@SQL)

Some things to note:

1 - I assume the table name has some type of extension that is a date? I used MM/DD/YYYY and removed the slashes as a format for the suffix.

2 - The WHERE clause will only work if you are not using the time part of the variable.

For instance, 03/15/2016 00:00:00 would be date without time entry. If not, you will have to use >= and < to grab all hours for a particular day.

3 - You are creating a table on the fly with this code. On the second execution, you will get a error unless you drop the table.

4 - You are not using the ON clause when joining table A to table R. To be ANSI compliant, move the WHERE clause to a ON clause.

5 - The actual calculation created by the CASE statement is not give a column name.

Issues 3 to 5 have to be solved on your end since I do not have the detailed business requirements. Have Fun.

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30
0

It should work using dynamic SQL to allow putting a dynamic table name:

DECLARE @SQL NVARCHAR(MAX) = N'
   SELECT CUENTA, SUM(COSTO_REC) COSTO_REC 
   INTO E09040_DEV.BI_PRO_COSTO_RECARGAS 
   FROM (                        
      SELECT a.*,(CASE
      WHEN COD_AJUSTE IN (''ELEC_TEXT'',''TFREPPVV_C'') THEN (A.VALOR)*(R.COSTO) ELSE 0 END)
      FROM Recargas_' + @MES + ' AS A,  BI_PRO_LISTA_COSTOS_RECARGAS AS R
      WHERE R.ANO_MES = ' + CAST(@MES AS VARCHAR(32)) + '
   ) D  
   GROUP BY CUENTA'

EXECUTE (@SQL)
Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164