Share via

sql server stored pro - setvar

nononame2021 256 Reputation points
2022-01-26T09:39:14.683+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,246 Reputation points
    2022-01-26T10:56:23.153+00:00

    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.

    0 comments
  2. Dan Guzman 9,231 Reputation points
    2022-01-26T11:30:24.72+00:00

    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.

    0 comments
  3. LiHong-MSFT 10,046 Reputation points
    2022-01-27T02:05:38.933+00:00

    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.

    0 comments