December 31, 2014 at 9:45 am
Is there a way to validate a string of T-SQL in T-SQL?
In this case, I generate a bunch of code that is to be executed.
Is there any way to ensure the code is error-free prior to executing it?
P
December 31, 2014 at 9:53 am
Up on the toolbar there is a button with a checkmark on it, three buttons to the right of the !Execute button. Click that and it will parse the T-SQL code for correct syntax, etc. If you get error msgs, you can double click on the error message and it will take you to the offending line. (Most of the time, depending on where there are hard CRs in your code. Those confuse it.)
If you see this, Command(s) completed successfully. then try to Execute your SQL. It may work.
If not, and you get other errors at this point, they are run-time errors and are probably being caused by faulty logic in your code. If so, happy debugging!
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
December 31, 2014 at 9:58 am
Thanks for the reply Sigerson,
What I'm actually after is a T-SQL procedure or function to which I can pass the variable containing the generated code...
I tried a roll-your-own proc which had a try-catch in which I SET PARSEONLY ON execute sp_executesql @code SET PARSEONLY OFF, but it kept throwing an error on valid code.
December 31, 2014 at 10:42 am
Sorry, was reading too fast! But I've learned a little more about SQL Server so I'm repaid for looking stupid. 😛
PARSEONLY sounds like it ought to work. Except BOL doesn't mention sp_executesql, just setting the flags ON/OFF in the query window. It seems to be warning agains it. Maybe it's the use of sp_executesql that's causing it to fail>
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
December 31, 2014 at 12:32 pm
schleep (12/31/2014)
Thanks for the reply Sigerson,What I'm actually after is a T-SQL procedure or function to which I can pass the variable containing the generated code...
I tried a roll-your-own proc which had a try-catch in which I SET PARSEONLY ON execute sp_executesql @code SET PARSEONLY OFF, but it kept throwing an error on valid code.
You don't say what the error is, but I'm guessing it has something do with @code not being declared. You SET PARSEONLY ON for just that portion of the code:
execute sp_executesql @code
But, the value of @code is not available there. Try enclosing the DECLARE and value of @code within the SET PARSEONLY statement.
If that isn't the error you are getting, post more information about the error and it might help us troubleshoot this. It could also be that it just won't work within a stored procedure.
-SQLBill
December 31, 2014 at 12:46 pm
yep a rough example: set parseonly makes sure it's syntactically correct, but doesn't check for object existance:
DECLARE @code varchar(max) = 'SELECT * FROM syys.tables';
EXEC ('SET NOCOUNT ON;SET PARSEONLY ON;'+ @code)
EXEC(@code)--Error Invalid object name 'syys.tables'.
SET @code = 'SELECT * FROM FROM syys.tables';
EXEC ('SET NOCOUNT ON;SET PARSEONLY ON;'+ @code) --Error Incorrect syntax near the keyword 'FROM'.
Lowell
December 31, 2014 at 2:23 pm
Quick thought, think you are better off trying sp_describe_first_result_set or sys.dm_exec_describe_first_result_set for this.
😎
December 31, 2014 at 2:44 pm
I dont know if this behaves any different from parseonly but...
set noexec on
select 1
set noexec off
Edit I don't think this would gain you anything over parseonly. The only difference I can tell is that noexec saves down a query plan whereas parseonly does not. It still doesn't validate objects.
January 2, 2015 at 7:00 am
DECLARE @code varchar(max)
SET @code = 'SELECT * from FROM sys.tables';
BEGIN TRY
EXEC ('SET PARSEONLY ON; ' + @code + '; SET PARSEONLY OFF;')
SELECT 'Success'
END TRY
BEGIN CATCH
SELECT 'Error'
END CATCH
This correctly gets to the catch block.
However, when the code is correct, 'SELECT * FROM sys.tables', the command executes. Which I definitely don't want.
sp_describe_first_result_set: it's in my BOL (SQL2K8 R2), but I get
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_describe_first_result_set'.
when I try to run the example from BOL...
January 2, 2015 at 7:35 am
Quick example on how to use the system procedure sp_describe_first_result_set to validate the dynamic SQL
😎
USE tempdb;
GO
/* Query to test */
DECLARE @SQL_STR NVARCHAR(MAX) = N'SELECT [INVALID_COLUMN_NAME] FROM sys.tables';
/* Table variable to catch the output of the sp_describe_first_result_set procedure
*/
DECLARE @RES TABLE
(
is_hidden BIT NULL
,column_ordinal INT NULL
,name SYSNAME NULL
,is_nullable BIT NULL
,system_type_id INT NULL
,system_type_name SYSNAME NULL
,max_length INT NULL
,precision INT NULL
,scale INT NULL
,collation_name SYSNAME NULL
,user_type_id INT NULL
,user_type_database SYSNAME NULL
,user_type_schema SYSNAME NULL
,user_type_name SYSNAME NULL
,assembly_qualified_type_name SYSNAME NULL
,xml_collection_id INT NULL
,xml_collection_database SYSNAME NULL
,xml_collection_schema SYSNAME NULL
,xml_collection_name SYSNAME NULL
,is_xml_document BIT NULL
,is_case_sensitive BIT NULL
,is_fixed_length_clr_type BIT NULL
,source_server SYSNAME NULL
,source_database SYSNAME NULL
,source_schema SYSNAME NULL
,source_table SYSNAME NULL
,source_column SYSNAME NULL
,is_identity_column BIT NULL
,is_part_of_unique_key BIT NULL
,is_updateable BIT NULL
,is_computed_column BIT NULL
,is_sparse_column_set BIT NULL
,ordinal_in_order_by_list BIT NULL
,order_by_is_descending BIT NULL
,order_by_list_length BIT NULL
,tds_type_id INT NULL
,tds_length INT NULL
,tds_collation_id INT NULL
,tds_collation_sort_id INT NULL
)
BEGIN TRY
INSERT INTO @RES
EXEC sp_describe_first_result_set @SQL_STR;
/* Successful parsing, no error message */
SELECT 0 AS ERR_NO
,N'' AS ERR_MSG;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ERR_NO
,ERROR_MESSAGE() AS ERR_MSG ;
END CATCH
Error output
ERR_NO ERR_MSG
----------- -------------------------------------------
207 Invalid column name 'INVALID_COLUMN_NAME'.
January 2, 2015 at 7:40 am
Eirikur,
This is select @@version:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
I ran your code, and this is what I get:
ERR_NOERR_MSG
2812 Could not find stored procedure 'sp_describe_first_result_set'.
January 2, 2015 at 8:04 am
My bad, sp_describe_first_result_set is only available in 2012 and later, you will have to use the FMTONLY set statement. It will bring back an empty result set if the query is successfully parsed but it will not execute the query.
😎
USE tempdb;
GO
/* Query to test */
DECLARE @SQL_STR NVARCHAR(MAX) = N'
SET FMTONLY ON;
SELECT * FROM sys.tables;
SET FMTONLY OFF;
';
BEGIN TRY
EXEC ( @SQL_STR );
/* Successful parsing, no error message */
SELECT 0 AS ERR_NO
,N'' AS ERR_MSG;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ERR_NO
,ERROR_MESSAGE() AS ERR_MSG ;
END CATCH
January 2, 2015 at 8:46 am
That's working in the back-end Eirikur, thank you!!
I have to dump the empty result set into a #tmp table so the front-end doesn't puke, but that's a minor detail.
January 2, 2015 at 10:54 am
schleep (1/2/2015)
That's working in the back-end Eirikur, thank you!!I have to dump the empty result set into a #tmp table so the front-end doesn't puke, but that's a minor detail.
Quick thought, the result set's structure depends on the actual query being parsed, better use a "count" workaround for this, consider the example code below.
😎
USE tempdb;
GO
/* Query to test */
DECLARE @SQL_STR NVARCHAR(MAX) = N'
SET FMTONLY ON;
DECLARE @TINT INT = 0;
SELECT @TINT = COUNT(*) FROM (
/* Query starts */
SELECT * FROM sys.partitions
/* Query ends */
) AS X
SET FMTONLY OFF;
';
BEGIN TRY
EXEC ( @SQL_STR );
/* Successful parsing, no error message */
SELECT 0 AS ERR_NO
,N'' AS ERR_MSG;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ERR_NO
,ERROR_MESSAGE() AS ERR_MSG ;
END CATCH
January 2, 2015 at 11:13 am
Very clever.
Thanks again!
P
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply