Checking a string for valid TSQL

Question

Post reply

Checking a string for valid TSQL

John Deupree

SSCommitted

Points: 1705
More actions
September 1, 2016 at 10:08 am

#329642

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

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

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

😎

John Deupree SSCommitted Points: 1705 More actions · Answer 3

Thanks for the reply.

Sorry this is for 2008R2. 🙁

That's new for 2012

John Deupree

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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

John Deupree SSCommitted Points: 1705 More actions · Answer 5

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

Sergiy SSC Guru Points: 110208 More actions · Answer 6

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

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?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions