dynamic table names in stored procedure...

Hello all,

Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name?

Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...

Cheers,
Justinyou will have to create a dynamic query something on these lines

declare @.table nvarchar(20)
set @.table = 'Customers'
declare @.sql nvarchar(100)
set @.sql = 'select * from ' + @.table
exec sp_executesql @.sql

i used the northwind database as an example for this and this example works on the customers table...

so if your column names will not make a difference then you will need to create a dynamic query on these lines and execute it|||http://www.sommarskog.se/dynamic_sql.html|||SQL Injection - look it up or better still read Jesse's link.

If you must do this then I would recommend at a minimum that you only allow acceptable values for @.tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @.tablename and only execute the final string if there is.|||SQL Injection - look it up or better still read Jesse's link.

If you must do this then I would recommend at a minimum that you only allow acceptable values for @.tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @.tablename and only execute the final string if there is.

Paranoia ... a man after my own heart.