Checking a string for valid TSQL

  • I want to be able to pass a string into a stored proc and then check to see if it is a valid TSQL statement. Does anyone know if that is possible?

    John Deupree

    John Deupree

  • The only thing that comes to my mind is sp_get_query_template.

    It is used to get the parameterized form of a query, but it fails if the query is not valid.

    BEGIN TRY

    DECLARE @my_templatetext nvarchar(max)

    DECLARE @my_parameters nvarchar(max)

    EXEC sys.sp_get_query_template @querytext = N'SELECT * FROM master.sys.databases WHERE name === ''master'''''

    ,@templatetext = @my_templatetext OUTPUT

    ,@parameters = @my_parameters OUTPUT;

    END TRY

    BEGIN CATCH

    IF ERROR_NUMBER() = 102

    BEGIN

    PRINT 'The statement is invalid: ' + ERROR_MESSAGE()

    END

    END CATCH

    -- Gianluca Sartori

  • Another option is to use either sp_describe_first_result_set or the sys.DM... version of it.

    😎

  • Thanks for the reply.

    Sorry this is for 2008R2. 🙁

    That's new for 2012

    John Deupree

  • Maybe this can give you an idea.

    CREATE TABLE Test( id int);

    DECLARE @SQL NVARCHAR(1000) = 'DROP TABLE Test;';

    EXEC( 'SET NOEXEC ON; ' + @SQL);

    SELECT * FROM Test;

    DROP TABLE Test;

    EDIT: Changed PARSEONLY to NOEXEC. That way the statement should compile and not only parse.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for the reply.

    Co-worker gave me this code so it seems that your solution or either of these work. Only problem is that they don't check for valid objects (columns or tables) in DB.

    DECLARE @cSql NVARCHAR(MAX);

    DECLARE @my_templatetext NVARCHAR(MAX);

    DECLARE @my_parameters NVARCHAR(MAX);

    --SET @cSql = 'CREATE NONCLUSTERED INDEX idx_Fred ON xyz.Fred ( FredId ) INCLUDE (FredStatus)'

    SET @cSql = 'SELECT * FROM dbo.fred'

    -- uncomment this to use EXEC or sys.sp_executesql

    --SET @cSql = 'set noexec on; ' + @csql

    BEGIN TRY

    -- either this

    EXEC sys.sp_executesql

    @cSql;

    --or this

    EXEC (@cSql);

    -- or this works

    EXEC sys.sp_get_query_template

    @querytext = @cSql

    , @templatetext = @my_templatetext OUTPUT

    , @parameters = @my_parameters OUTPUT;

    END TRY

    BEGIN CATCH

    SELECT ERROR_NUMBER()

    , ERROR_MESSAGE();

    RETURN;

    END CATCH;

    John Deupree

  • John Deupree (9/1/2016) Only problem is that they don't check for valid objects (columns or tables) in DB.

    Columns are checked. If a columns does not exist sp_get_query_template throws an error.

    Tables are not.

    It's exactly the same way as when you create a normal stored procedure.

    _____________
    Code for TallyGenerator

  • For some reason, the whole idea of passing a SQL statement to a stored procedure to see if it's valid makes me shudder a bit. I have to ask why you'd want to do this and if this is exposed to a front end of some type. If a rogue statement were passed to it, would it recognize that the TRUNCATE or DROP TABLE could do damage?

Viewing 8 posts - 1 through 7 (of 7 total)

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