none
SQL Syntax Validation

    Question

  • Aside from the concerns about security and embedded statements, etc, is there a way to submit a Query to SQL Server for validation?

     

    I'm creating an application that will allow the user to sort of roll their own in creating filter expressions.  I'd like to be able to submit these for syntax validation before submitting for actual processing.

     

    Thanks.

     

    J

    Friday, August 31, 2007 12:37 AM

Answers

  • Thanks, Mani...  Your post lead me to searching further...  And I found the PARSEONLY flag.  That's the guy I needed.

    Thanks.

     

    J

     

    Friday, August 31, 2007 2:23 AM

All replies

  • Are you constructing this sql in application code or embedding this inside a stored procedure? You definitely need to check the syntax of the queries generated before actually sending for query processing. How to do it depends on where you are writing these queries. If inside a SP then you should be able to capture teh sql inside a variable and print the variable to test the code. someting like...

    Code Snippet

    create proc get_authors123

    as

    declare @str nvarchar(4000)

    set @str = N'select au_id, au_lname from dbo.authors' --I have simplified the details here and we know its more complex than this stmt.

    select @str

    exec sp_executesql @str

    return

    go

     

     

    If you are doing this in application code then it gets tricky to separate out the application variables. I am not sure if there is an easy way out of this. If there is pre-defined pattern to construct the queries then you should be able to embed them in stored procedures and pass in parameters to SP's to adjust accordingly.

     

    Friday, August 31, 2007 1:53 AM
  • The sql statements will be constructed in a separate application.

     

    I will be submitting through an ADO .NET call.  I can submit it and trap for exceptions, but I'd rather have some way to validate the syntax before submitting it.

     

    Thanks.

     

    J

     

    Friday, August 31, 2007 2:03 AM
  • You can call the sql script with "SET FMTONLY ON;" + YOUR SQL STATEMENT. If it fails then you have to fix those reported errors. otherwise your SQL is clean & you can proceed to execute the SQL to get the result.
    Friday, August 31, 2007 2:17 AM
  • Thanks, Mani...  Your post lead me to searching further...  And I found the PARSEONLY flag.  That's the guy I needed.

    Thanks.

     

    J

     

    Friday, August 31, 2007 2:23 AM


  • hi ,
    I am making a web application in which i am taking the query from input text box and i want to validate the syntax of SQL query.How can it be validated ?
    Monday, October 22, 2007 12:22 PM
  • Did you actually read the posts in this thread?  The answer is in the post directly above yours.  The PARESONLY flag.

    Monday, October 22, 2007 5:23 PM