Note that this can be extended to access any object name (column, database, server) in a variable.


This is a simple problem to solve but has a number of consequences.
It is often because you wish to pass a table name to a stored procedure
perhaps processing all tables in a database.

Of course
select * from @TableName
will not work as the variable will not be resolved as containing the name of a table.

The method used here is dynamic sql - executing a built string.

Create procedure s_ProcTable
@TableName varchar(128)
as

declare @sql varchar(4000)
	select @sql = 'select rows = count(*) from [' + @TableName + ']'
	exec (@sql)
go


Now executing this will give the result.
Note the [] around the name in case it contains invalid characters.
You may also have to deal with the owner.

now
exec s_ProcTable 'sysobjects'
exec s_ProcTable 'syscolumns'
gives the result for these tables


Warning


Consider

declare @s varchar(128)
select @s = 'sysobjects]' + char(13) + char(10) + 'go'  + char(13) + char(10) + 'select * from [syscolumns'
or even just
select @s = 'sysobjects] select * from [syscolumns'
exec s_ProcTable @s

This is called sql injection and the given sp will allow the user to execute any query they wish on your server.
They can even drop databases if they have permission.
Do not try to use this with input from a non-trusted user without being very careful.

It also means that the user will need to be given permission on the objects accessed as this will not be inherited from the SP.


Alternatives


Create procedure s_ProcTable
@TableName varchar(128)
as

	if @TableName = 'sysojects'
		select rows = count(*) from sysojects
	else if @TableName = 'syscolumns'
		select rows = count(*) from syscolumns
go

Means you will need to cater for all tables but gets round all the problems above.