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.
12,654 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 40,656 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 No comments

  2. Dan Guzman 9,206 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 No 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 No comments