Passing table name in to procedure as a variable

[DaveJunior]

Hi, I am a quite new at SQL and wondered if someone could give me a hand...

I am trying to pass the table name that I am searching through into a stored procedure as a variable however it gives me the error message: "The variable name '@TheTable' has already been declared. Variable names must be unique within a query batch or stored procedure." Any ideas? The code I am using is:

CREATE PROC Forum__CheckNoOfReplies @TheTable varchar(100), @TheID int(4) As
	SELECT AllowedReplies
	FROM @TheTable
	WHERE ID = @TheID
GO

I am calling it like:

EXEC CheckNoOfReplies 'Forum_Classfieds', '54'

Any help would be great, cheers.

 

[swampBoogie]

Why do you need to send the table as a variable name? Do you have multiple tables with the same structure?

It can be done with dynamic Tsql

CREATE PROC Forum__CheckNoOfReplies (
@TheTable varchar(100), @TheID int(4))
As
exec('SELECT AllowedReplies
FROM ' + @TheTable +
' WHERE ID = ' + cast(@TheID as varchar(12)))


If you have this need it is often an indication that your database design is a bit off.

 

[sem]

I suppose it highly depends on your platform. In Oracle you may use EXECUTE IMMEDIATE for single-row query or REF CURSORs for multiple rows. Though I'm sure that any dynamic sql is not the matter of ANSI. Regards, Dima