What are the challenges you face when working across database platforms? Take the survey
Options

Format SQL function fails if SQLCMD mode variable is present

eklein@ahd.comeklein@ahd.com Posts: 17 Bronze 2
edited April 7, 2016 10:11AM in SQL Prompt
The following code
 SELECT  * FROM dbo.sheet_master 
 WHERE RPT_REC_NUM IN ( SELECT  RPT_REC_NUM
             FROM  Cost_report_$(HCycleN).dbo.rrn_status
 WHERE Rec_status = 'dropped'
 ) 
 
 
will not format useing Version 7.1.0.315

Code is valid and will execute in SQLCMD mode

if the variable is substituted making the code
 
 SELECT  * FROM dbo.sheet_master 
 WHERE RPT_REC_NUM IN ( SELECT  RPT_REC_NUM
             FROM  Cost_report_151231.dbo.rrn_status 
 WHERE Rec_status = 'dropped'
 ) 
 

It will format

Comments

  • Options
    Aaron LAaron L Posts: 596 New member
    Hi,

    SQL Prompt doesn't currently support partially replacing an identifier with a SQLCMD variable. This is an enhancement we'll consider in the future, for now a workaround is to wrap the identifier with square brackets, ie:
     SELECT  * FROM dbo.sheet_master 
     WHERE RPT_REC_NUM IN ( SELECT  RPT_REC_NUM
                 FROM  [Cost_report_$(HCycleN)].dbo.rrn_status
     WHERE Rec_status = 'dropped'
     ) 
    

    Hope that helps!

    Aaron.
  • Options
    Thank you The work around is easy enough for now
Sign In or Register to comment.