table name as parameter to stored procedure

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

I have a stored procedure that uses several SQL statements, including cursors, on a table. I would like that table name to be flexible, being passed as a parameter to the procedure: create procedure do_the_work(@tbl_name varchar(50)) AS update @tbl_name set col_xyz = 0 . . . declare the_cursor cursor for select * from @the_table . . .

Informix has a "CREATE SYNONYM" statement that works nicely for this. The only thing I can think of with SQL Server is to do an EXEC() every time the table name is used, which gets quite messy, and I'm not sure what it does with the performance. Is there a clean way to do this?

-- Anonymous, November 02, 1999

Answers

Robert,

You are correct that the solution in Transact SQL requires the use of the EXEC statement. When the EXEC statement is encountered the SQL statement will be compiled. Thus, the statement will operate quickly, but the downside is that you will have to wait for the statement to be compiled first. If this causes too much delay and there is only a limited set of table names that can be passed in, you could use a CASE statement based on the table name. Thus, you would save the compilation time.

Good Luck,

Eric

-- Anonymous, November 02, 1999


Robert,

If you are using SQL Server 7.0, you can use sp_executesql with parameters.

Good Luck,

Eric

-- Anonymous, November 09, 1999


Moderation questions? read the FAQ