T-SQL to call the SQL Parse "function"

  • Does anyone know if it is possible to call SQL Server's Statement parser (syntax checking) via T-SQL?

    I'm looking to see if it's possible to validate a field that contains SQL code programmatically.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • I've played with this a couple of times, Jason; i've used it to chop a SQL statement into elements, so i could play with building my own formatter, and of course basic error checking without talking to a server.

    here's a vb.net example.

    'adding reference Imports Microsoft.SqlServer.Management.Sdk.Sfc

    Private Sub btnSQLParse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSQLParse.Click

    Dim sql As String

    Dim script As Microsoft.SqlServer.SqlParser.SqlCodeDom.SqlScript

    sql = " SELECT * FROM FROM sys.objects"

    'a shared public function, no need to construct an object, so no object.parse method is used.

    script = Microsoft.SqlServer.SqlParser.Parser.Parser.Parse(sql)

    For Each s As Microsoft.SqlServer.SqlParser.Parser.Error In script.Errors

    Debug.Print(s.Message)

    Next

    End Sub

    and the errors i got from this malformed statement, without needing a database/connection or anything else:

    Incorrect syntax near 'FROM'.

    Incorrect syntax near '.'.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (11/1/2012)


    I've played with this a couple of times, Jason; i've used it to chop a SQL statement into elements, so i could play with building my own formatter, and of course basic error checking without talking to a server.

    here's a vb.net example.

    'adding reference Imports Microsoft.SqlServer.Management.Sdk.Sfc

    Private Sub btnSQLParse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSQLParse.Click

    Dim sql As String

    Dim script As Microsoft.SqlServer.SqlParser.SqlCodeDom.SqlScript

    sql = " SELECT * FROM FROM sys.objects"

    'a shared public function, no need to construct an object, so no object.parse method is used.

    script = Microsoft.SqlServer.SqlParser.Parser.Parser.Parse(sql)

    For Each s As Microsoft.SqlServer.SqlParser.Parser.Error In script.Errors

    Debug.Print(s.Message)

    Next

    End Sub

    and the errors i got from this malformed statement, without needing a database/connection or anything else:

    Incorrect syntax near 'FROM'.

    Incorrect syntax near '.'.

    That's pretty darned close. :w00t:

    I guess I could wrap this up and use CLR, although I'm not sure if I'll be able to do this in client DB's which is why I was looking for a straight SQL approach.

    Also, your output leads me to believe that it has issues with schema? Why the error on the '.' ?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • SELECT * FROM FROM sys.objects

    , yeah the parse assumes the second FROM is a table name, and so the alias 'sys.tables' is an error.

    a longer, better error message comes from this:

    Debug.Print(IIf(s.IsWarning, "Warning: ", "Error:") & "Line Number: " & s.Start.LineNumber.ToString & " ColumnNumber:" & s.Start.ColumnNumber & " Error:" & s.Message)

    Error:Line Number: 1 ColumnNumber:16 Error:Incorrect syntax near 'FROM'.

    Error:Line Number: 1 ColumnNumber:24 Error:Incorrect syntax near '.'.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Ha Ha...I need to sloooooow down. I totally missed the two FROM's

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • A pure SQL solution, using Lowell's example might be like this:

    DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'

    BEGIN TRY

    EXEC (@SQL)

    END TRY

    BEGIN CATCH

    PRINT ERROR_NUMBER()

    PRINT ERROR_SEVERITY()

    PRINT ERROR_STATE()

    PRINT ERROR_PROCEDURE()

    PRINT ERROR_LINE()

    PRINT ERROR_MESSAGE()

    END CATCH

    But:

    1. I would only use it for SELECTs (not INSERT, UPDATE or DELETE) for obvious reasons.

    2. Long running SELECTs will be problematic.

    3. Results from the SELECT (if it runs) are of course returned but its probably they can be ignored.

    In the CATCH, the nice feature is that you can return the exact syntax failure message to the client.

    I find this to be an interesting question so I will look around further to see if SQL has a callable syntax parser. My initial search yielded nothing.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Still haven't found the definitive answer but I did think of this approach.

    It might work a little better and could conceivably be used with INSERTs, UPDATEs and DELETEs!

    DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'

    BEGIN TRY

    EXEC ('CREATE PROCEDURE DUMMY WITH RECOMPILE AS BEGIN; ' + @SQL + ';END');

    DROP PROCEDURE DUMMY;

    END TRY

    BEGIN CATCH

    PRINT ERROR_NUMBER();

    PRINT ERROR_SEVERITY();

    PRINT ERROR_STATE();

    PRINT ERROR_PROCEDURE();

    PRINT ERROR_LINE();

    PRINT ERROR_MESSAGE();

    END CATCH;

    Basically you're using your SQL to create a stored procedure and only the CREATE of the stored procedure is executed, which syntax checks your SQL. The CATCH executes if the dynamic SQL won't compile. It should probably be executed within the database context and the logged in user would need to have permissions granted to create a SP.

    While it would not catch issues where a referenced table is the wrong name or something due to deferred name resolution when you create the SP, it would catch cases where a column name is wrong and the table already exists.

    Of course, you may want to use a more definitive name for the SP (instead of DUMMY). Something with a timestamp perhaps (but then the DROP would also need to be dynamic SQL).

    Edit: The RECOMPILE causes SQL Server to not cache an execution plan for the SP. I was hoping there might be a way to turn off deferred name resolution on CREATE but apparently it was not to be.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Good idea dwain, but I do not want to/ can not execute the statements.

    I need only to check the syntax.

    Also, the syntax can be any valid sql. So if the input was a "create procedure" statement, yours would fail would fail.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg (11/1/2012)


    Good idea dwain, but I do not want to/ can not execute the statements.

    I need only to check the syntax.

    Also, the syntax can be any valid sql. So if the input was a "create procedure" statement, yours would fail would fail.

    Maybe load the query into a variable, SET SHOWPLAN_XML ON, and then EXEC the variable dumping the result set to yet another variable???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/1/2012)


    Jason Selburg (11/1/2012)


    Good idea dwain, but I do not want to/ can not execute the statements.

    I need only to check the syntax.

    Also, the syntax can be any valid sql. So if the input was a "create procedure" statement, yours would fail would fail.

    Maybe load the query into a variable, SET SHOWPLAN_XML ON, and then EXEC the variable dumping the result set to yet another variable???

    I tried something like that but it seems to mask the error.

    SET SHOWPLAN_XML ON

    GO

    DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'

    BEGIN TRY

    EXEC (@SQL)

    END TRY

    BEGIN CATCH

    PRINT ERROR_NUMBER()

    PRINT ERROR_SEVERITY()

    PRINT ERROR_STATE()

    PRINT ERROR_PROCEDURE()

    PRINT ERROR_LINE()

    PRINT ERROR_MESSAGE()

    END CATCH

    GO

    SET SHOWPLAN_XML OFF


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Jeff Moden (11/1/2012)


    Jason Selburg (11/1/2012)


    Good idea dwain, but I do not want to/ can not execute the statements.

    I need only to check the syntax.

    Also, the syntax can be any valid sql. So if the input was a "create procedure" statement, yours would fail would fail.

    Maybe load the query into a variable, SET SHOWPLAN_XML ON, and then EXEC the variable dumping the result set to yet another variable???

    Respectfully, EXEC is not an option, even if it were wrapped in a transaction and rolled back. I was hoping there was an undocumented feature or cleaver work around. :crying:

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Not even with SET NOEXEC ON?

    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
  • GilaMonster (11/2/2012)


    Not even with SET NOEXEC ON?

    That might be it.

    So how would I (or is it possible) to do this inside a procedure and capture the output.

    In other words, I have a table with supposedly valid SQL inside and I need to valid each instnace.

    CREATE TABLE dbo.testData

    (nDex int IDENTITY

    ,sqlText nvarchar(MAX)

    ,isValid BIT DEFAULT(0))

    GO

    -------------------------------------------

    INSERT dbo.testData (sqlText)

    VALUES

    ('SELECT * FROM sys.databases')

    ,('SELECT * FROM FROM sys.databases')

    ,('CREATE TABLE dbo.test (a int, b int)

    GO

    EXEC dbo.someProc @p1=''xyz''')

    ,('Gobly gook')

    GO

    -------------------------------------------

    I've tried ...

    CREATE PROCEDURE dbo.isValid

    (@nDex INT)

    AS

    BEGIN

    DECLARE @sql NVARCHAR(MAX)

    SELECT @sql = sqlText FROM dbo.testData WHERE nDex = @nDex

    BEGIN TRY

    SET NOEXEC ON;

    PRINT @sql

    EXEC (@sql)

    SET NOEXEC OFF;

    UPDATE dbo.testData SET isValid = 1 WHERE nDex = @nDex

    END TRY

    BEGIN CATCH

    UPDATE dbo.testData SET isValid = 0 WHERE nDex = @nDex

    END CATCH

    END

    GO

    Thoughts?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Try this:

    DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'

    SET @SQL = 'SET NOEXEC ON;

    ' + @SQL + '

    SET NOEXEC OFF;'

    BEGIN TRY

    EXEC (@SQL)

    END TRY

    BEGIN CATCH

    PRINT 'Error here: ' + ERROR_MESSAGE()

    END CATCH

    SET NOEXEC OFF;

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (11/2/2012)


    Try this:

    DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'

    SET @SQL = 'SET NOEXEC ON;

    ' + @SQL + '

    SET NOEXEC OFF;'

    BEGIN TRY

    EXEC (@SQL)

    END TRY

    BEGIN CATCH

    PRINT 'Error here: ' + ERROR_MESSAGE()

    END CATCH

    SET NOEXEC OFF;

    Almost there!!!!!

    It seems multi-line statements/batches seem to have problems and are returned as invalid. :hehe:

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 15 posts - 1 through 15 (of 18 total)

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