Format SQL function fails if SQLCMD mode variable is present

eklein@ahd.com

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

Aaron L

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.

eklein@ahd.com

Thank you The work around is easy enough for now