SQL SERVER – How to Set Variable and Use Variable in SQLCMD Mode
- by Pinal Dave
Here is the question which I received the other day on SQLAuthority Facebook page. Social media is a wonderful thing and I love the active conversation between blog readers and myself – actually I think social media adds lots of human factor to any conversation. Here is the question -
“I am using sqlcmd in SSMS – I am not sure how to declare variable and pass it, for example I have a database and it has table, how can I make the table variable dynamic and pass different value everytime?”
Fantastic question, and here is its very simple answer. First of all, enable sqlcmd mode in SQL Server Management Studio as described in following image.
Now in query editor type following SQL.
:SETVAR DatabaseName “AdventureWorks2012″
:SETVAR SchemaName “Person”
:SETVAR TableName “EmailAddress“
USE $(DatabaseName);
SELECT *
FROM $(SchemaName).$(TableName);
Note that I have set the value of the database, schema and table as a sqlcmd variable and I am executing the query using the same parameters.
Well, that was it, sqlcmd is a very simple language to master and it also aids in doing various tasks easily.
If you have any other sqlcmd tips, please leave a comment and I will publish it with due credit.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology Tagged: sqlcmd
