Parse a SQL statement in SQL Server to check syntax validity - Similar to dbms_sql.parse in Oracle

Question

I have a requirement in a project where some users insert some expressions in a table that later on will be added to a select statement for running a query. Before running the query itself in the final tool, I need to use a function to check the validity of the statement. Basically, I need to parse the statement to check the validity of the expression. The database is SQL Server 2014.

I know how to do it in Oracle using this small piece of code:

Oracle

create or replace function check_sql( p_sql in CLOB ) return boolean
authid current_user
is
v_cur int;
begin
v_cur:= dbms_sql.open_cursor();
dbms_sql.parse(v_cur,p_sql,dbms_sql.native);
dbms_sql.close_cursor(v_cur);
return true;
exception
when others then
return false;
end;
/

SQL> set serveroutput on size unlimited
SQL> declare
v_result varchar2(20);
is_ok boolean;
begin
  is_ok := check_sql ( 'select 1 from dual' ) ;
  if is_ok
  then 
    v_result := 'OK' ;
  else
    v_result := 'ERROR' ;
  end if;
  dbms_output.put_line(v_result);
end;
/

OK

SQL> set serveroutput on size unlimited
SQL> declare
v_result varchar2(20);
is_ok boolean;
begin
  is_ok := check_sql ( 'select 1 from duala' ) ;
  if is_ok
  then 
    v_result := 'OK' ;
  else
    v_result := 'ERROR' ;
  end if;
  dbms_output.put_line(v_result);
end;
/

ERROR

Can anyone tell me how to do something similar, if possible, in Transact SQL?

Thank you for your help

SET PARSEONLY ON probably does what you need - see answers to stackoverflow.com/questions/13316328/… — Ed Harper
– Ed Harper, Commented Aug 31, 2020 at 9:09

Jacek Wróbel · Accepted Answer · 2020-08-31 09:13:50Z

1

How about combination of sp_prepare and sp_unprepare:

DECLARE @P1 INT;  
DECLARE @Cmd NVARCHAR(200) = N'SELECT database_id, name FROM sys.databases'
BEGIN TRY
    EXEC sp_prepare @P1 OUTPUT, NULL, @Cmd;  
    EXEC sp_unprepare @P1
    PRINT N'CORRECT'
END TRY
BEGIN CATCH
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_MESSAGE() AS ErrorMessage;  
    PRINT N'INCORRECT'
END CATCH

Incorrect statement will throw an exception

DECLARE @P1 INT;  
DECLARE @Cmd NVARCHAR(200) = N'SELECT database_id, name FROM sys.databases2'
BEGIN TRY
    EXEC sp_prepare @P1 OUTPUT, NULL, @Cmd;  
    EXEC sp_unprepare @P1
    PRINT N'CORRECT'
END TRY
BEGIN CATCH
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_MESSAGE() AS ErrorMessage;  
    PRINT N'INCORRECT'
END CATCH

answered Aug 31, 2020 at 9:13

Jacek Wróbel

1,23211 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roberto Hernandez Over a year ago

thank you for your answer. How can I add this to a procedure in transact sql that accepts the table name as input parameter ?

Collectives™ on Stack Overflow

Parse a SQL statement in SQL Server to check syntax validity - Similar to dbms_sql.parse in Oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related