2 
SET @SQLSTATEMENT = 'INSERT INTO #MAX_STORAGE
                        SELECT MAX(A.[ROW]) 
                        FROM
                            (SELECT * 
                             FROM [DATABASE].[dbo].[Refined_Est_Probability_09_MODIFIED] 
                             WHERE 
                                 [FIPST_ENT] = ' + @FIPST_ENT + ' 
                                 AND [FIPCNTY_ENT] = ' + @FIPCNTY_ENT + '
                                 AND [SIC_ENT] = ' + @SIC2_ENT + '
                                 AND [FMSZ_ENT] = ' + @FMSZENT_ENT + '
                                 AND [ESTABLISHMENTS_AVAILABLE_FMSZEST <= ' + @MAXIMUM_FMSZEST+'] > 0) A'

EXEC(@SQLSTATEMENT)

I was running the dynamic SQL query above as part of a stored procedure I had written and got the following error:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'A'.

I then changed my query so that it looked like this (eliminated the alias A):

SET @SQLSTATEMENT =
    'INSERT INTO #MAX_STORAGE
        SELECT 
           MAX([ROW]) 
        FROM
           (SELECT * 
            FROM [DATABASE].[dbo].[Refined_Est_Probability_09_MODIFIED] 
            WHERE [FIPST_ENT] = ' + @FIPST_ENT + ' 
              AND [FIPCNTY_ENT] = ' + @FIPCNTY_ENT + '
              AND [SIC_ENT] = ' + @SIC2_ENT + '
              AND [FMSZ_ENT] = ' + @FMSZENT_ENT + '
              AND [ESTABLISHMENTS_AVAILABLE_FMSZEST <= ' + @MAXIMUM_FMSZEST + '] > 0)'

EXEC(@SQLSTATEMENT)

But I still ran into an error (this time different):

Msg 102, level 15, state 1, line 9
Incorrect syntax near ')'

I declared the following variables earlier in the procedure with their respective data types/lengths seen next to them:

  • @FIPST_ENT CHAR(2)
  • @FIPCNTY_ENT CHAR(3)
  • @SIC2_ENT CHAR(2)
  • @FMSZENT_ENT CHAR(1)
  • @MAXIMUM_FMSZENT CHAR(1)
  • @SQLSTATEMENT VARCHAR(MAX)

Before this dynamic SQL statement was reached in the stored procedure, the temporary table #MAX_STORAGE was already created and contains only one column of datatype int.

Am I missing something I'm doing wrong? Any help would be greatly appreciated.

Thanks.

Improve this question
9
  • does it work if you put hard coded values in for the variables? DOes just the inner query work? Does just the middle select (everythihng but insert). Commented Nov 13, 2014 at 20:44
  • @MarshallTigerus Yeah it does work with hard coded values in place of the variables. I had tested that before. I'm starting to think this isn't where the issue is with my procedure! I'll keep searching Commented Nov 13, 2014 at 20:47
  • This line looks odd: AND [ESTABLISHMENTS_AVAILABLE_FMSZEST<='+@MAXIMUM_FMSZEST+'] > 0)' do you have column names with a <= in them? Commented Nov 13, 2014 at 20:53
  • @DStanley I have a few columns in my table that have different @MAXIMUM_FMSZEST values (ranging from 1-9, A-C). That's why I needed dynamic SQL because that column changes every iteration depending on what the @MAXIMUM_FMSZEST value is. Thus, if @MAXIMUM_FMSZEST = 9, the column I want to be greater than 0 would be [ESTABLISHMENTS_AVAILABLE_FMSZEST<=9]. But to answer your question, yes the <= part is in the column name. Commented Nov 13, 2014 at 20:56
  • 1
    Actually, rather than guessing, do a PRINT @SQLSTATEMENT to see what is wrong ;-) Commented Nov 13, 2014 at 21:22

1 Answer 1

Reset to default
2

At bare minimum, you need to enclose string fields in escaped-single-quotes within the Dynamic SQL. The adaptation I show below is based on this comment on the Question:

FIPST_ENT is numeric in nature (i.e. 01-50) but cast as a character. Likewise with the other FIPCNTY_ENT and SIC2_ENT. FMSZENT is cast as a character but is sometimes numeric (i.e. 1-9) and other times non-numeric (i.e. A-C).

So it seems that only FMSZENT needs the escaped-single-quotes.

Also, using a derived query requires an alias. So whatever the initial problem was, you then introduced a new parse error by removing the alias ;-).

SET @SQLSTATEMENT =

'INSERT INTO #MAX_STORAGE
SELECT MAX(tmp.[ROW]) FROM

(SELECT * FROM [DATABASE].[dbo].[Refined_Est_Probability_09_MODIFIED] 
WHERE [FIPST_ENT] = '+@FIPST_ENT+' 
AND [FIPCNTY_ENT] = '+@FIPCNTY_ENT+'
AND [SIC_ENT] = '+@SIC2_ENT+'
AND [FMSZ_ENT] = '''+@FMSZENT_ENT+'''
AND [ESTABLISHMENTS_AVAILABLE_FMSZEST<='+@MAXIMUM_FMSZEST+'] > 0) tmp;'

Now, when it comes to debugging Dynamic SQL, the first step should be looking at what SQL you actually constructed, as it might not be what you think it should be:

PRINT @SQLSTATEMENT;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

That seems to have fixed it. I prefer using CHAR(39) though instead of a bunch of apostrophes in a row. Thanks for your help!
@ultimate8 : you are welcome. And regarding CHAR(39) vs double-single-quote, yeah, I have seen a few people do that. I am so used to the double-single quote that I never remember about CHAR(39), but certainly nothing wrong with it, and yes, it does improve readability :).
my only remaining question is why do you need to enclose @FMSZENT_ENT but not anything else when all of the variables (@FIPST_ENT, @FIPCNTY_ENT, and @SIC2_ENT, and even @FMSZENT_ENT) are all defined as char type. Is it because @FMSZENT_ENT is unlike the others and has non-numeric values in it?
@ultimate8 : Essentially yes. Be careful to not confuse the two layers of what you are doing. The outer layer (so to speak) is the dynamic SQL string. Any variables that you use to concatenate into the @SQLSTATEMENT string need to likewise be strings because you can just add an INT or DATETIME value to a VARCHAR. If your other variables were INT datatypes, you would have to use CONVERT(VARCHAR(30), @Variable) in order to concatenate them in. The inner layer is the query being executed, and AND [FMSZ_ENT] = A is not valid since A is a string and needs to be in quotes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.