Select with a TableName by Parameter 2

[Kristjan]

I need help with a procedure...
The problem is that i need use a select but the table refered is parameter (variable, etc), and i don´t know how i do this.
Example:

CREATE PROCEDURE ProcedureName @idvariable int,@NombreTabla varchar(20)
AS
...
DECLARE @FechaSalida datetime, @FechaEntrada datetime, @otravariable int

SELECT @FechaSalida = FechaHoraSalida, @FechaEntrada = FechaHoraEntrada FROM @NombreTabla
where Idvariable = @idvariable

...etc

There is the problem how can i use the name of the table (@NombreTabla) as parameter?
I try anothers forms but nothing works.
this works:
EXEC("SELECT FechaHoraSalida, FechaHoraEntrada FROM "+ @NombreTabla + " where 2 = Idvariable" )
but...need return and use other variable

tnx...

 

[Kristjan]

Ok... Im looking and looking... and find sp_executesql.. tnx Terry. but ther problem persist

look:
*-*-* this work:
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @FechaSalida datetime, @FechaEntrada datetime, @MesFS int, @MesFE int
DECLARE @SPTName varchar(16), @IdSalidaPlanta int
SET @IdSalidaPlanta = 2

SET @SQLString = N'SELECT @FechaSalidaOUT = FechaHoraSalida, @FechaEntradaOUT = FechaHoraEntrada
FROM PRE_SalidaPlanta WHERE IdSalidaPlanta = @IdSalidaPlantaIN '

SET @ParmDefinition = N'@IdSalidaPlantaIN int,
@FechaSalidaOUT datetime OUTPUT, @FechaEntradaOUT datetime OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@IdSalidaPlantaIN = @IdSalidaPlanta,
@FechaSalidaOUT = @FechaSalida OUTPUT,
@FechaEntradaOUT = @FechaEntrada OUTPUT

SELECT @FechaSalida, @FechaEntrada
>>>>>>>>>>>>>>>>>>>>>>>>>
RESULT>
--------------------------- ---------------------------
2002-11-13 11:04:00.000 2002-12-15 20:22:00.000

(1 row(s) affected)
--*/*-*/*/-*/-*/-*/-*/*-/-*/-*/-*/-*/-/-*/-*/*-

ok but i need also the @TableName
like this: --but doesn´t work

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @FechaSalida datetime, @FechaEntrada datetime, @MesFS int, @MesFE int
DECLARE @SPTName varchar(16), @IdSalidaPlanta int
SET @SPTName = 'PRE_SalidaPlanta'
SET @IdSalidaPlanta = 2

SET @SQLString = N'SELECT @FechaSalidaOUT = FechaHoraSalida, @FechaEntradaOUT = FechaHoraEntrada
FROM @SPTNameIN WHERE IdSalidaPlanta = @IdSalidaPlantaIN '

SET @ParmDefinition = N'@SPTNameIN varchar(16), @IdSalidaPlantaIN int,
@FechaSalidaOUT datetime OUTPUT, @FechaEntradaOUT datetime OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@SPTNameIN = @SPTName,
@IdSalidaPlantaIN = @IdSalidaPlanta,
@FechaSalidaOUT = @FechaSalida OUTPUT,
@FechaEntradaOUT = @FechaEntrada OUTPUT
SELECT @FechaSalida, @FechaEntrada

>>>>>>>>>>>>>>>>>>>>>>>>>
RESULT> ERROR:
Server: Msg 170, Level 15, State 1, Line 0
Line 3: Incorrect syntax near '@SPTNameIN'.

--------------------------- ---------------------------
NULL NULL

(1 row(s) affected)
--*-*-*-*-*-*-/*-/*/-**-/**/-/*/-/-++-*-*/-

mmm, what can i do?!!!

 

[sunila7]

Hi,

Try this..........

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @FechaSalida datetime, @FechaEntrada datetime, @MesFS int, @MesFE int
DECLARE @SPTName varchar(16), @IdSalidaPlanta int
SET @SPTName = 'PRE_SalidaPlanta'
SET @IdSalidaPlanta = 2

SET @SQLString = N'SELECT @FechaSalidaOUT = FechaHoraSalida, @FechaEntradaOUT = FechaHoraEntrada
FROM '+ @SPTName +' WHERE chargeid = @IdSalidaPlantaIN '

SET @ParmDefinition = N'@IdSalidaPlantaIN int,
@FechaSalidaOUT datetime OUTPUT, @FechaEntradaOUT datetime OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@IdSalidaPlantaIN = @IdSalidaPlanta,
@FechaSalidaOUT = @FechaSalida OUTPUT,
@FechaEntradaOUT = @FechaEntrada OUTPUT

SELECT @FechaSalida, @FechaEntrada


Hope it helps


Sunil

 

[Kristjan]

I'm already find the solution!!!
tnx Sunil... it works too!!!

im close!! ... je je je, only use that @TableName without the sp_executesql... and work... easy but i spend all day... i need more practice!!!