Passing table name to Stored Procedure

[jojo41300000]

Hi,

I am trying to pass the table name to stored procedure and then use the passing table name in select, update or delete query statement.
When I tried to do that, I got the error such as '@tablename must be declared'.

The example syntax would be like below.

CREATE PROCEDURE procedure_name
@tablename varchar(50)
AS

BEGIN

SELECT *
FROM @tablename

END


Is there anyway I can pass the table name to stored procedure?

[bwestover]

You can do it pretty much like you've got it set up with the input parameter.

The only thing is you cant execute a select statement directly from your string variable like that.

Instead, build the entire statement where you hope to use that variable into new string variable and then EXEC the whole thing

Example:

Declare @sqlstr varchar(250)
 
@sqlstr = 'Select * From ' + @tablename
 
(Exec @sqlstr)
 

[jojo41300000]

Thanks.

Actually, I am trying to use the tablename paraemeter with Cursor.
When I tried this:

1. DECLARE @SQLSTR VARCHAR(250),
2. @ID INT
3. SET @SQLSTR = 'SELECT ID FROM ' + @tablename

4. DECLARE MyCursor FOR (Exec (@SQLSTR))
5. OPEN MyCursor
6. FETCH MyCursor INTO @ID

I got the error message :: Incorrect syntax near the keyword 'FOR'

how can I fix the error?

[azimmer]

Look at this: http://www.thescripts.com/forum/thread686090.html