Google
 

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"

4 comments:

Hrishikesh Desai said...

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

nitin said...

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

Hesham A. Amin said...

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

nitin said...

ya...exactly..does it possible?