Passing table name dynamically?

[diggermf]

Hi All,

I have to modify a stored procedure in which the table name should be passed dynamically. is it possible? if so, could some one please advice on this.

For example:

SELECT * FROM <@table_Name>

@table_Name should be passed dynamically. Please adivce

 

[bborissov]

In that case you use use so called Dynamic SQL:

DECLARE @sql varchar(8000)
SET @sql = 'SELECT * FROM '+@table_name
EXEC (@sql)

Use this method very carefully and only if you have no other choice. It is one of the performance killers and also you will be opened for injection attacks if you didn't parse your parameters very carefully. What if your @table_name variable contains:

RealTable; DROP TABLE VeryImportantOne

Then you will execute that statemenet:

SELECT * FROM RealTable; DROP TABLE VeryImportantOne

Which is perfectly valid T-SQL command.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLSister]

Seriously read up on SQL injection attacks. Making your database vunerable to real security issues is a dumb thing to do especially if it is only to save programmers time. Dynamic SQl is a very bad thing to be avoided not embraced.

http://www.unixwiz.net/techtips/sql-injection.html

"NOTHING is more important in a database than integrity." ESquared