How to use a Table Variable name in a Stored Proc?

Hi All,
I'm trying to figure out how to use a variable within a stored proc to be
used as the table name.
I am passing in the name of a table to a stored proc and getting the table
name form sysobjects. This works fine; but when I go to use that name in a
query, inside the same Stored Procedure, it fails.
Example:
CREATE PROCEDURE dbo.Select_Data
@.tbl VarChar(30)
AS
SELECT T = sysobjects.name
FROM sysobjects
WHERE sysobjects.name = @.tbl
SET NOCOUNT ON
/*This Part Fails*/
SELECT *
FROM T
/*This Part Fails*/
SET NOCOUNT OFF
GO
Any help on this would be appreciated.
Thanks,
John.sp_executesql
Es.
DECLARE @.Sqlstat nvarchar(50)
set @.Sqlstat='SELECT * FROM ' + @.Tbl
exec sp_executeSql @.Sqlstat
Francesco Anti
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:On9DYabRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I'm trying to figure out how to use a variable within a stored proc to be
> used as the table name.
> I am passing in the name of a table to a stored proc and getting the table
> name form sysobjects. This works fine; but when I go to use that name in
> a query, inside the same Stored Procedure, it fails.
> Example:
> CREATE PROCEDURE dbo.Select_Data
> @.tbl VarChar(30)
> AS
> SELECT T = sysobjects.name
> FROM sysobjects
> WHERE sysobjects.name = @.tbl
> SET NOCOUNT ON
> /*This Part Fails*/
> SELECT *
> FROM T
> /*This Part Fails*/
> SET NOCOUNT OFF
> GO
> Any help on this would be appreciated.
> Thanks,
> John.
>|||http://www.sommarskog.se/dynamic_sql.html#Dyn_table
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:On9DYabRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I'm trying to figure out how to use a variable within a stored proc to be
> used as the table name.
> I am passing in the name of a table to a stored proc and getting the table
> name form sysobjects. This works fine; but when I go to use that name in
> a query, inside the same Stored Procedure, it fails.
> Example:
> CREATE PROCEDURE dbo.Select_Data
> @.tbl VarChar(30)
> AS
> SELECT T = sysobjects.name
> FROM sysobjects
> WHERE sysobjects.name = @.tbl
> SET NOCOUNT ON
> /*This Part Fails*/
> SELECT *
> FROM T
> /*This Part Fails*/
> SET NOCOUNT OFF
> GO
> Any help on this would be appreciated.
> Thanks,
> John.
>|||Don't create generic procs like this. It's just plain bad design and
serves no useful purpose: slow; insecure; difficult to maintain in
code. The standard pattern is to create separate procs for each table
SELECT, and it should be very easy to generate those semi-automatically
from the info schema tables if you like.
David Portas
SQL Server MVP
--