Saturday, February 29, 2020

Check if your dynamic T-SQL statement is valid

Validating SQL Queries with a Stored Procedure in SQL Server

In this post, I will walk you through creating a stored procedure that can be used to validate SQL queries. The procedure checks if the SQL query is syntactically correct and can be executed successfully without any errors.

The stored procedure I'll show you is designed to check if a query can run without any issues by parsing it first before execution.

Here’s the SQL code for the stored procedure:

CREATE PROCEDURE IsValidSQLs (@sql VARCHAR(MAX))
AS
BEGIN
    BEGIN TRY
        SET @sql = 'SET PARSEONLY ON;' + @sql;
        EXECUTE(@sql);
    END TRY
    BEGIN CATCH
        RETURN(0); -- Failure
    END CATCH;
    RETURN(1); -- Success
END; -- IsValidSQLs

Testing the Procedure

To test the procedure, let's try a scenario where we know the query will fail. In this example, we're referencing a non-existent table called T:

-- Fail Scenario
DECLARE @retval INT;
EXECUTE @retval = IsValidSQLs 'SELECT IIF(val, 0, 1) FROM T'; -- T does not exist
SELECT @retval;
GO

When executed, this query will return 0 because the table T doesn't exist in the database.

Success Case

Now, let’s test it with a query that should pass. In this case, we will create a temporary table #T and then try running a query against it:

-- Success Scenario
CREATE TABLE #T(id INT IDENTITY(1,1), val VARCHAR(100));
DECLARE @retval INT;
EXECUTE @retval = IsValidSQLs 'SELECT val FROM #T'; -- #T exists
SELECT @retval;

This time, the procedure will return 1, indicating that the query is valid and syntactically correct.

By using this stored procedure, you can easily validate SQL queries before executing them, ensuring they won’t cause errors in your applications. This can be particularly useful in scenarios where dynamic SQL is being executed.

at

Popular Posts