3

Imagine you try to run the following query:

SELECT *
FROM dbo.Customers c
WHERE id = @id
    AND dateTimeCreated = @date

You'll get an error message along the lines of Must declare the scalar variable "@id".

As I understand it, this happens during the initial parsing of the query, before any code is executed.

Is it possible to get SQL Server to report all the missing variables at once, or is this just how the query parser works?

4

Error list

The error list might give you what you need:

You could open View --> Error List in ssms

enter image description here

To get all the build + intelliSense errors:

enter image description here


Differences between error list & the messages tab on query execution:

Error list

The Error List contains all syntax and semantic errors found by IntelliSense in the Database Engine Editor. This list of errors is dynamically updated as you edit Transact-SQL scripts. The list includes all errors that the editor has found in each Transact-SQL script. The editor does not stop parsing a file after encountering errors in a script. In SQL Server 2008, IntelliSense in the Database Engine Editor does not support all Transact-SQL syntax elements. The Error List contains only errors from the Transact-SQL syntax that is supported by IntelliSense.

Messages Tab

The Messages tab at the bottom of the Database Engine Query Editor window displays all errors and messages that are returned by the SQL Server Database Engine when a Transact-SQL script is executed. This list does not change until you execute the script again. The Database Engine stops parsing a batch after it finds one or two compile errors; therefore, the Messages tab might not list all errors in a script. Source

So to answer the question:

Is it possible to get SQL Server to report all the missing variables at once, or is this just how the query parser works?

Using general query excecution this is not possible as the parsing stops after one or two compile errors, but you could use the error list.

  • Thanks! Since I'm actually calling this query from inside .NET I was hoping it'd be possible to get the errors results from the query execution, but your answer makes it clear that's not possible without some kind of a SQL parsing library like the one built into SSMS. – Petter Brodin Nov 13 at 11:00
  • @PetterBrodin Happy to help :). There could definitely be better solutions than the one shown here, hopefully a new answer is added with a better solution. – Randi Vertongen Nov 13 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.