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.
