Lets say that i have a set of tables that are similar in structure, Is there a way to pass a tablename to a stored procedure and have it select the records from it?

For instance

Create Procedure sp_GetMeRecords
(
@TableName
)

Set nocount on

if exists(Select ID FROM @TableName)
begin
  SELECT * FROM @TableName
end

go

when i do this, it keeps telling me that @TableName isn't declared!

please help thanks!

could be as follows:

Create Procedure sp_GetMeRecords
@TableName varchar(100)
as

exec('if exists(Select ID FROM ' + @TableName + ')
     SELECT * FROM ' + @TableName




Ahmed Ali
Software Developer

Also check out undocumented stored procedures at: http://www.mssqlcity.com/Articles/Un...000UndocSP.htm


sp_MSforeachtable
Sometimes, you need to perform the same actions for all tables in the database. You can create cursor for this purpose, or you can also use the sp_MSforeachtable stored procedure to accomplish the same goal with less work.

For example, you can use the sp_MSforeachtable stored procedure to rebuild all the indexes in a database:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

Hi,

You will have to use EXEC or EXECUTE statements for dynamic table names.

Thanks,

Lalit
Life Means More...;)