How to pass table name dynamically in T-SQL

Question

I want to write sample procedure to count the records present in specific tables, here i will fetch the table name s from the text file. Fo this i just write sample procedure to fetch th table names from text file and storing in tem table but while i am assigning and passing the table name i am unable to do it can any one suggest how to do it by simple example

This isn't the sort of thing you should do in T-SQL. If you want to assemble queries, you should do it in a middle-tier component. — Thomas
– Thomas, Commented Feb 23, 2011 at 6:34

Anil Soman · Accepted Answer · 2011-02-23 06:59:27Z

2

You can build dynamic sql using table name parameter and execte it using EXEC as shown below.

Create procedure Proc1 @TableName varchar(MAX) as .. ... DECLARE @sqltxt varchar(1000) select @sqltxt = 'select count(*) from ' + quotename(@TableName) exec (@sqltxt) ... ...

edited Feb 23, 2011 at 6:59

answered Feb 23, 2011 at 6:27

Anil Soman

2,4677 gold badges41 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thomas Over a year ago

If you are going to do this, you should use Quotename: Set @sqltxt = 'Select ... From ' + Quotename( @TableName ).

Kashif Over a year ago

exec (@sql) should be exec (@sqltxt)

Thomas Over a year ago

+1 for the corrected version, although the actual correct answer should be "don't do this".

Anil Soman Over a year ago

@Thomas - Ya.That's why I had asked a query / feature request in meta for marking a comment as an answer. If they would have enabled this feature, then your query could have been marked as answer! meta.stackexchange.com/questions/77900/…

Collectives™ on Stack Overflow

How to pass table name dynamically in T-SQL

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related