Feb 9, 2012

Sql query to validate or check syntax of sql statement in sql server


Sql query  or stored procedure to validate or check only syntax of sql statement without executing the query in sql server

CREATE PROC ValidateSqlSyntax(
@CustomQuery AS NVARCHAR(MAX),
@StatusMsg AS VARCHAR(MAX) OUTPUT
)
AS
BEGIN


BEGIN TRY

IF @CustomQuery IS NOT NULL BEGIN 

--SET @CustomQuery = REPLACE(@CustomQuery,'''','''''')
SET @CustomQuery =  'SET NOEXEC ON; ' + @CustomQuery + ' ; SET NOEXEC OFF;'

END

EXECUTE SP_EXECUTESQL @CustomQuery

SET @StatusMsg = 'Success'

END TRY
BEGIN CATCH
SET @StatusMsg = ERROR_MESSAGE() 
END CATCH
END


To execute:


DECLARE @StatusMsg AS VARCHAR(MAX)
EXECUTE ValidateSqlSyntax 'UPDATE tblTest SET Id =5;SELECT 1', @StatusMsg OUTPUT
SELECT @StatusMsg


Note: Stored procedure  ValidateSqlSyntax will check only syntax of sql queries. I will not check the objects name,columns name etc. It will validate the column name only if is already present in the database. 

3 comments:

  1. kashan4/16/2012

    excellent i can use in validating crystal reports bug on handling single and multiple parameter value

    ReplyDelete
  2. I am a DB Administrator .It is Useful For Me. Excellent and valid Presentation .

    ReplyDelete
  3. Anonymous7/05/2013

    Thank you for your answers ,, its wonderful ,, please try every one if you had a situation like this ,,

    ReplyDelete