sql server stored pro - setvar

Question

i found a stored procedure but don't know how to use :SETVAR, the script is shown as below:

---

CREATE PROCEDURE sp_testing
AS
BEGIN

:SETVAR ABCDbName "[test123]"

select distinct ID into #temp1 from $(ABCDbName).dbo.tabl1 ;

end

---

may i know what is the function of <:SETVAR ABCDbName "[test123]">
is [test123] a database name?
why it need to add $ in sql script?

Answer 1

That's impossible an existing stored procedure, because it's not valid Transact-SQL. SSDT project script may be?

That's scripting syntax for the SqlCmd tool or SSMS with a query window in SQLMD mode.
See sqlcmd Utility, there you can find all commands/syntax.

In SqlCmd you can use variable for object name, in T-SQL it's not possible.
In this case [test123] is indeed the database name.

Answer 2

may i know what is the function of <:SETVAR ABCDbName "[test123]">

ABCDbName is a SQLCMD scripting variable understood by the SQLCMD utility and SSMS query windows in SQLCMD mode (menu option Query-->SQLCMD mode). :SETVAR ABCDbName "[test123]" instructs the tools to set variable ABCDbName to value [test123]. When the script is executed by the tool, references to the variable within the script ($(ABCDbName)) are replaced with [test123] and SQLCMD commands (starting with :) are removed because they are not T=SQL statements. The final T-SQL batch sent to SQL Server with the script in your question is:

CREATE PROCEDURE sp_testing  
AS  
BEGIN  
  
select distinct ID into #temp1 from [test123].dbo.tabl1;  
  
END  

See sqlcmd - Use with Scripting Variables for more information.

Answer 3

Hi @nononame2021

what is the function of <:SETVAR ABCDbName "[test123]">
why it need to add $ in sql script?

Simply put, ':SETVAR' and '$' are used to declare variable and use variable in SQLCMD Mode.
The ':SETVAR' only works in SQL command mode, so first of all, you need to enable sqlcmd mode in SQL Server Management Studio. This can be done by going to the "Query" menu, and selecting "SQLCMD mode" .

Then have a test on this code:

:SETVAR DatabaseName "your_database_name"  
:SETVAR SchemaName "your_SchemaName"  
:SETVAR TableName "your_table_name"  
USE $(DatabaseName);  
SELECT *  
FROM $(SchemaName).$(TableName);  

Finally

is [test123] a database name?

You will find the answer to this is Yes

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.