CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Taco585
Taco585

Reputation: 5

Change a table name in SQL Server procedure

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

Upvotes: 0

Views: 81

Answers (2)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

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)

Upvotes: 0

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

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.

Upvotes: 1

Related Questions