Dynamic table listing

[ai3rules]

Hi all,

New to SQL and having some trouble with something very basic.

I'd like to create a procedure where I can feed any table name to a parameter and perform SELECT etc. on that table. This will be done from ASP.

Something like the following:


ALTER PROCEDURE [dbo].[dyn_table_list]
@table varchar(50)
AS
BEGIN

SET NOCOUNT ON;

SELECT *
FROM @table

END


I get an error when I do that though saying I have to declare the @table variable. What am I missing?

Thank you for any help,
Israel

[mattyblah]

Select does not work like that. You can't use a variable for a table in that context.

Try this:

ALTER PROCEDURE [dbo].[dyn_table_list]
@table varchar(50)
AS
BEGIN

DECLARE @sql nvarchar(4000)
SET @sql = N'SELECT * FROM dbo.' + @table
exec dbo.sp_executesql @sql
END

[ai3rules]

Thank you very much. That works great.