send a table name variable to a stored procedure

Question

i try to do this but i can't do it, help please:

Send the name of a table as a variable to a stored procedure to make a select query whit that variable. Example:

USE [Andes]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[epesos_ing_log]*/
@Tbl_OC_Llamada VARCHAR(128),-- TABLE's NAME
@Tbl_Est_Llamada VARCHAR(128) ,-- TABLE's NAME
@tbl_Resul_Llamada VARCHAR(128) -- TABLE's NAME

as

select  iId, 
        datepart(year, dFechaHora) as año_periodo, 
        datepart(MM, dFechaHora) as mes_periodo, 
        cast(dFechaHora as date) as dFecha, 
        datepart(HH, dFechaHora) as T_hora, 
        dFechaHora, dFin, 
        datediff (second, dFechaHora, dFin) as tiempo,
        sNumeroTelefono, 
        'Emisión Ext' as tipo, 
        (iAgenteEmisor) as Agente

from @Tbl_OC_Llamada 
    inner join @Tbl_Est_Llamada 
    on ELL.id_estado = T.iUltimoEstado
    inner join @tbl_Resul_Llamada 
    on RLL.id_res_llamada = T.iResultadoLlamada   

where sDireccion = 's' 
    and len (sNumeroTelefono) > 4
    and desc_estado in ('Hablando','Iniciada')
    and iAgenteEmisor in (select cod_ejecutiva
     from andes.dbo.BORRADOR_EJECUTIVA)

You can't do it that way. You'll have to use dynamic SQL for that... but what are you even trying to do? You have hard-coded in your proc the join conditions for the tables. Your entire proc will break if you don't pass in the correct tables and in the correct order. Why even accept parameters in the first place? — Siyual, Mar 07 '17 at 19:08
Thank you for your clarification, the prameters are , Supposedly, the name of the table I try to use — g.cifuentes, Mar 07 '17 at 19:21
You can't parameterize identifiers in sql, but even if you could, this means if you will pass the wrong table name to this procedure it will raise an error since all the column names (and values) are hard coded. — Zohar Peled, Mar 08 '17 at 09:54
This is usually a sign of a broken data model - when you have multiple tables with the same structures, it's usually a sign that they should be a single table with one or more columns containing additional data (often, you'll find the some of what should be modelled as *data* has instead been modelled as *metadata*, such as being embedded in the table *names*) — Damien_The_Unbeliever, Mar 08 '17 at 10:30

score 0 · Accepted Answer · edited May 23 '17 at 12:17

0

This is a problem which can be solved by using Dynamic SQL. Please look at the answers on this question: Table name as variable

edited May 23 '17 at 12:17

Community

1
1

answered Mar 08 '17 at 10:19

DK5

317
3
15

send a table name variable to a stored procedure

1 Answers1