Parsing T-SQL

Task: syntax parsing and checking db object existence in SQL query
Solution: see Source code No. 4


Source code No. 1: Syntax parsing of SQL

DECLARE @SqlQuery NVARCHAR(4000)
SET @SqlQuery = 'SELECT [ID] FRON [dbo].[Parsing_T-SQL]'
EXEC ('SET FMTONLY ON ' + @SqlQuery)

Figure 1: SSMS SQL query result – syntax parsing of SQL

Source code No. 2: Table existence check

DECLARE @SqlQuery NVARCHAR(4000)
SET @SqlQuery = 'SELECT [ID] FROM [dbo].[Parsing_T-SQL]'
EXEC ('SET FMTONLY ON ' + @SqlQuery)

Figure 2: SSMS SQL query result – table existence check

Notes:

• syntax parsing and checking db object existence in SQL query is valid equally for all commands of DML set (it means: SELECT/INSERT/UPDATE/DELETE)
• functionally identical for EXEC and EXEC sp_executesql
• option SET NOEXEC ON and SET PARSEONLY ON executed syntax parsing only but not db object existence check (db object existence check may be carried out by means of SYSOBJECTS, SYSCOLUMNS etc.)
• the structure of BEGIN TRAN-ROLLBACK TRAN is not suitable because not all operations may be rollbacked (e.g. DROP DATABASE)

Source code No. 3: Syntax parsing and checking the existence in SQL query with user’s parameter (i.e. input)

DECLARE @SqlQuery NVARCHAR(4000)
SET @SqlQuery = 'UPDATE [dbo].[Parsing_T-SQL] SET [Value] = ''Test'' WHERE [ID] = @ID'
EXEC ('SET FMTONLY ON ' + @SqlQuery)

Figure 3: SSMS SQL query result – syntax parsing and checking the existence in SQL query with user’s parameter (input)

Note: PARSE is OK but for EXECUTE see Figure 3

Solution: replacement @ID = 1 (see Source code No. 4)

Source code No. 4: Syntax parsing and checking the existence in SQL query with user’s parameter (i.e. input) and replacement @ID = 1

DECLARE @SqlQuery NVARCHAR(4000)
SET @SqlQuery = 'UPDATE [dbo].[Parsing_T-SQL] SET [Value] = ''Test'' WHERE [ID] = 1'
EXEC ('SET FMTONLY ON ' + @SqlQuery)

Additional reference:

• SqlWrite
  by Luke Breuer