How to use variable in t-sql query

Question

Today I get my database name like that :

set @databaseNameTXT = 'NewStat1DB';

And then I insert the data to the right table like that:

IF @databaseNameTXT = 'NewStat1DB' 
    BEGIN
        INSERT INTO [NewStat1DB] (wStat_id) values(@wStat_id)
    END

IF @databaseNameTXT = 'NewStat2DB'
    BEGIN
        INSERT INTO [NewStat2DB] (wStat_id) values(@wStat_id)
    END

How can I use the variable inside the t-sql and run it, something like:

INSERT INTO [@databaseNameTXT] (wStat_id) values(@wStat_id)

Thanks

Oded · Accepted Answer · 2012-09-12 19:52:27Z

4

You need to use dynamic SQL for this, though you need to be careful of SQL Injection.

Database, schema, table and column names cannot be variables - the only way to do this is use dynamic SQL.

For example (this is vulnerable to SQL Injection):

sp_executesql 'INSERT INTO [' + @databaseNameTXT + 
                '] (wStat_id) values(' + @wStat_id + ');';

I suggest reading the linked article - it is a comprehensive treatment of the subject of dynamic SQL.

answered Sep 12, 2012 at 19:52

Oded

501k102 gold badges900 silver badges1k bronze badges

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

5 Comments

Tomalak Over a year ago

This is prone to SQL injection and should not be used.

HLGEM Over a year ago

If you are trying to send in a table name dynamically, there is close to a 100% chance that you have a poor design.

Jim Over a year ago

SQL injection attacks are not a factor in every use case. If he's not getting data from a publicly accessible source, it doesn't really matter. For example, he could be doing back-end update with a script that the SQL server agent runs or that he runs manually.

paparazzo Over a year ago

@Jim You think attacks only come from the public? You don't think a script can be hacked? I never trust external data even if it came from from my own code.

Jim Over a year ago

No, I think you're being silly and paranoid by telling someone not to use a great tool without knowing the situation in which they would be using it.

Taryn · Accepted Answer · 2012-09-12 19:52:40Z

1

You can use Dynamic SQL for this:

declare @query nvarchar(max)

set @query = 'INSERT INTO ' + QUOTENAME(@databaseNameTXT) + '(wStat_id) 
                values('+@wStat_id+')'

exec(@query)

answered Sep 12, 2012 at 19:52

Taryn

249k57 gold badges374 silver badges409 bronze badges

Collectives™ on Stack Overflow

How to use variable in t-sql query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related