Incorrect syntax error T-SQL SP SS2K5

Question

Post reply

Incorrect syntax error T-SQL SP SS2K5

Randy Anthony

SSC-Addicted

Points: 498
More actions
January 14, 2010 at 1:39 pm

#218222

Hi y'all,
Can't find anything related to this, so here goes... I get the Incorrect syntax near the keyword 'Group' error when trying to save this in T-SQL. Am I using the added parameter incorrectly?
Declare @AuditQuesTypeParameter As VarChar(20)
Begin
If @AuditYear < 1
Select @AuditYear = Null
If @AuditYear = 1 Set @AuditQuesTypeParameter = '1 And 4'
Else
Set @AuditQuesTypeParameter = '13 And 16'
Select tblAuditQuesLkup.AuditQuesNumber, tblAuditQuesLkup.AuditQuesType,
tblAuditQuesType.AuditQuesAuditType, tblAuditQuesLkup.AuditQuestion,
Count(tblAuditQuestions.AuditActualScore) As totNumPerQues
From tblAuditMain
Inner Join tblAuditQuestions On tblAuditMain.AuditNumber = tblAuditQuestions.AuditNumber
Inner Join tblAuditQuesLkup On tblAuditQuestions.AuditQuesNumber = tblAuditQuesLkup.AuditQuesNumber
Inner Join tblAuditQuesType On tblAuditQuesLkup.AuditQuesType = tblAuditQuesType.AuditQuesTypeArtKey
Where Coalesce(tblAuditMain.AuditDate,'') >= Coalesce(@BeginDate, Coalesce(tblAuditMain.AuditDate,''))
And Coalesce(tblAuditMain.AuditDate,'') <= Coalesce(@EndDate, Coalesce(tblAuditMain.AuditDate,''))
And Coalesce(tblAuditMain.AuditYear, '') = Coalesce(@AuditYear, Coalesce(tblAuditMain.AuditYear,''))
And tblAuditQuesLkup.AuditQuesType BETWEEN @AuditQuesTypeParameter
Group By tblAuditQuesLkup.AuditQuesNumber, tblAuditQuesLkup.AuditQuestion, tblAuditQuesType.AuditQuesAuditType,
tblAuditQuesLkup.AuditQuesType
Order By tblAuditQuesLkup.AuditQuesNumber

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Your between statement is incomplete.

Where Coalesce(tblAuditMain.AuditDate,'') >= Coalesce(@BeginDate, Coalesce(tblAuditMain.AuditDate,''))

And Coalesce(tblAuditMain.AuditDate,'') <= Coalesce(@EndDate, Coalesce(tblAuditMain.AuditDate,''))

And Coalesce(tblAuditMain.AuditYear, '') = Coalesce(@AuditYear, Coalesce(tblAuditMain.AuditYear,''))

And tblAuditQuesLkup.AuditQuesType BETWEEN @AuditQuesTypeParameter -- and what?????

Group By tblAuditQuesLkup.AuditQuesNumber, tblAuditQuesLkup.AuditQuestion, tblAuditQuesType.AuditQuesAuditType,

tblAuditQuesLkup.AuditQuesType

Order By tblAuditQuesLkup.AuditQuesNumber

What you're trying with the variables doesn't work. SQL interprets the entire value of the variable as the lower bound of the begin and it's (rightly) expecting the ending of the range. Anything in a variable is interpreted as a string literal, not part of the structure of the SELECT. Same reason you can't say this

SET @WherePortion = 'AND SomeColumn = 45'

SELECT * FROM SomeTable

WHERE Col1 = 'abc'

AND @WherePortion

You need to define two variables, one for the lower range of the between, one for the upper and then, in the select do

BETWEEN @lowerRange AND @upperRange

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The Grateful DBA SSC Veteran Points: 233 More actions · Answer 2

As Gail stated, your statement is incomplete. The only way you can pass in a "1 and 4" parameter and have it work would be to build the sql dynamically then execute it with an EXEC or SP_EXECUTESQL.

Randy Anthony SSC-Addicted Points: 498 More actions · Answer 3

Ok, so setting this:

If @AuditYear = 1 Set @AuditQuesTypeParameter = '1 And 4'

Else

Set @AuditQuesTypeParameter = '13 And 16'

and expecting this:

And tblAuditQuesLkup.AuditQuesType BETWEEN @AuditQuesTypeParameter

to work is incorrect. So, I originally tried to use a CASE statement, but that didn't work either. Other than passing the param into the SP, which I shouldn't do (protoco), how can I make this work? Thanks again for assisting.

The Grateful DBA SSC Veteran Points: 233 More actions · Answer 4

T-SQL doesn't allow you to build BETWEEN statements that way. If you truly have the need to pass in the "boundaries" that way then your best bet is to pass them both in individually and construct dynamic SQL with them. The definitive guide (as far as I'm concerned) on dynamic SQL is here: http://www.sommarskog.se/dynamic_sql.html

After you've had a chance to peruse that and still have questions, please feel free to post them here. I (and many others) will be glad to help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Randy Anthony (1/14/2010)
Ok, so setting this:
If @AuditYear = 1 Set @AuditQuesTypeParameter = '1 And 4'
Else
Set @AuditQuesTypeParameter = '13 And 16'
and expecting this:
And tblAuditQuesLkup.AuditQuesType BETWEEN @AuditQuesTypeParameter
to work is incorrect.

Yup. Variables are treated as literal values, never as part of the query structure.

Other than passing the param into the SP, which I shouldn't do (protoco), how can I make this work? Thanks again for assisting.

As I said, you need to define two variables (where you currently have one) set one to the lower limit of the range, the other to the upper limit of the range (where you're currently assigning that one variable to have both) and then do the between like this

BETWEEN @lowerRange AND @upperRange

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Troy G-471787 (1/14/2010)
If you truly have the need to pass in the "boundaries" that way then your best bet is to pass them both in individually and construct dynamic SQL with them. The definitive guide (as far as I'm concerned) on dynamic SQL is here: http://www.sommarskog.se/dynamic_sql.html

There is absolutely no need for dynamic SQL here.

All that needs doing is the single variable replaced with two, one with the lower bound, one with the upper bound of the between.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Randy Anthony SSC-Addicted Points: 498 More actions · Answer 7

Thanks, GM, I think a light bulb went on...

Thanks for the links, I'll pursue both.

The Grateful DBA SSC Veteran Points: 233 More actions · Answer 8

Gail - You're 100% right; I stand duly corrected. I should have gone back and reread the OP. Was thinking that there were more than just those 2 values being passed in.

Randy, what are you doing listening to me???!?!? 🙂