Parsing T-SQL

Posted: December 15, 2010 in MSSQL
Tags:

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
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
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)
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:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s