Check if your dynamic T-SQL statement is valid

I create this stored procedure and use in my scripts and codes.

CREATE PROCEDURE IsValidSQLs (@sql VARCHAR(MAX)) AS
BEGIN
    BEGIN TRY
        SET @sql = 'SET PARSEONLY ON;'+@sql;
        EXECUTE(@sql);
    END TRY
    BEGIN CATCH
        RETURN(0); --Fail
    END CATCH;
    RETURN(1); --Success
END; -- IsValidSQLs 
--Test:
--Fail
DECLARE @retval INT;
EXECUTE @retval = IsValidSQLs 'SELECT IIF(val, 0, 1) FROM T'; --T is not existing
SELECT @retval;
GO
--Success
CREATE TABLE #T(id INT IDENTITY(1,1),val VARCHAR(100));
DECLARE @retval INT;
EXECUTE @retval = IsValidSQLs  'SELECT val FROM  from #T'; --#T is existing
SELECT @retval;
Spread the love

Leave a Comment