Friday, May 16, 2008

How to check query syntax programmatically

A nice feature in MS SQL Server Management studio or the old good query analyzer is the ability to check query syntax before executing it by pressing (Parse) or (Ctrl+F5).

You also can provide this functionality in your application in case your application creates queries on the fly or has a query designer.

This can be done using the PARSEONLY option. Queries that run while PARSEONLY option is ON are parsed but not executed, For example:

SET PARSEONLY ON
Go
Select * from dbo.Books
Go
SET PARSEONLY OFF

The above Select will not be executed, it will only be parsed, the result will be:

Command(s) completed successfully.

Without returning any data.

It's simple, open the database connection, execute "SET PARSEONLY ON", then execute your query, then "SET PARSEONLY OFF"
at

4 comments:

  1. Hrishikesh DesaiDecember 30, 2008 2:18 PM

    Better would be to use SET NOEXEC ON as in below example as it will validate objects also

    SET NOEXEC ON
    Go
    Select * from dbo.Brooks
    Go
    SET NOEXEC OFF

    ReplyDelete
  2. nitinFebruary 14, 2012 9:16 AM

    hiiiiii
    how can i use these commands in SP.
    plz help me.
    thnx in adv.
    i uses sql server 2005/

    ReplyDelete
  3. Hesham A. AminFebruary 15, 2012 9:42 AM

    @nitin: Do you want to make a stored procedure that checks the syntax of a query?

    ReplyDelete
  4. nitinFebruary 15, 2012 10:40 AM

    ya...exactly..does it possible?

    ReplyDelete