How can we help you today? How can we help you today?

Format SQL function fails if SQLCMD mode variable is present

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
eklein
0

Comments

2 comments

Sort by
  • 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.
    Aaron L
    0
  • eklein
    Thank you The work around is easy enough for now
    eklein
    0

Add comment

Please sign in to leave a comment.