Variable Table Name in a Stored Procedure

[Rudi]

Hello Forum !

I want to have the tablename "dbo.Enbxxxx" as an additional parameter
for a procedure like this:

ALTER Procedure prcSucheUNR
(
@UNR int
)
As
set nocount on

SELECT ABRUFNR,
UNR,
STICHTAG,
Datum,
Inhalt
INTO #temp FROM dbo.Enb WHERE UNR = @UNR

For some reason:

@tablename varchar(11),

and: INTO #temp FROM @tablename WHERE .... does not work. :-(

I get the following syntax Error:
Zeile 33: Falsche Syntax in der Nähe von '@tablename'.

I'am sure it is possible but i don't know how.

Greetings

[MC]

You need to build and execute dynamic string. Something like:
execute 'SELECT *
INTO #temp
FROM '+@tablename+'
WHERE .... '
However, I would recommend visiting
http://www.sommarskog.se/dynamic_sql.html and reading about it before going
further...

MC
"Rudi" <fn**********@aareon.com> wrote in message
news:5d**************************@posting.google.c om...

Hello Forum !

I want to have the tablename "dbo.Enbxxxx" as an additional parameter
for a procedure like this:

ALTER Procedure prcSucheUNR
(
@UNR int
)
As
set nocount on

SELECT ABRUFNR,
UNR,
STICHTAG,
Datum,
Inhalt
INTO #temp FROM dbo.Enb WHERE UNR = @UNR

For some reason:

@tablename varchar(11),

and: INTO #temp FROM @tablename WHERE .... does not work. :-(

I get the following syntax Error:
Zeile 33: Falsche Syntax in der Nähe von '@tablename'.

I'am sure it is possible but i don't know how.

Greetings

[Dan Guzman]

See http://www.sommarskog.se/dynamic_sql.html for a discussion of dynamic
SQL.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rudi" <fn**********@aareon.com> wrote in message
news:5d**************************@posting.google.c om...

Hello Forum !

I want to have the tablename "dbo.Enbxxxx" as an additional parameter
for a procedure like this:

ALTER Procedure prcSucheUNR
(
@UNR int
)
As
set nocount on

SELECT ABRUFNR,
UNR,
STICHTAG,
Datum,
Inhalt
INTO #temp FROM dbo.Enb WHERE UNR = @UNR

For some reason:

@tablename varchar(11),

and: INTO #temp FROM @tablename WHERE .... does not work. :-(

I get the following syntax Error:
Zeile 33: Falsche Syntax in der Nähe von '@tablename'.

I'am sure it is possible but i don't know how.

Greetings

[Rudi]

Hello all !

This is what i was always looking for (and never found)
http://www.sommarskog.se/dynamic_sql.html

My problem is no longer a problem. :-)

Thank you for the quick response