I have a large repository of SQL scripts in SQLCMD syntax. Each script has 0 or more $(SomeParam) defined. We have a utility which executes the scripts and passes in the values for the $(params), essentially enable us to configure and automate builds for a wide range of data models.
Sometimes our build process fails because a script has a $(param) specified which is not configured in our utility. i.e. we have no value for this $(param) set
I would like to perform a test-run of these scripts prior to executing them. The test run should ensure that all required $(params) have been supplied to the script. The SQL itself should not be executed - I just want to know if it is compile-able. Is this possible to achieve using sqlcmd?
Alternatives Considered
I've considered one alternative of using a Powershell script to find matches for all $(param) in the file (using RegEx) and cross-checking that against our list of params in the utility. I'd prefer to retain all functionality either on the sqlcmd command-line or in the SQL scripts themselves if possible.
SET NOEXEC ON makes SQL Server enter a mode where statements are parsed and compiled but not executed, with the exception of the command SET NOEXEC OFF, which reverts things back to their original state. So, if statements end up syntactically invalid because of missing parameters, SET NOEXEC ON will catch it.
There is an important limitation to SET NOEXEC ON. The documentation misleadingly states that "this setting is useful for having SQL Server validate the syntax and object names in Transact-SQL code when executing", but, in fact, under SET NOEXEC ON, SQL Server will not resolve names to most objects at all. The following script will parse without error:
SET NOEXEC ON;
GO
CREATE TABLE Foo(ID INT PRIMARY KEY);
GO
INSERT Fooo(ID) VALUES (5); -- Oops, typo
That doesn't mean that no names are resolved at all. The following script will still fail:
SET NOEXEC OFF;
GO
CREATE FUNCTION Foo() RETURNS TABLE AS RETURN (SELECT 0 Bar);
GO
SET NOEXEC ON;
GO
SELECT dbo.Foo(); -- No error here, even though Foo can't be invoked this way
SELECT * FROM dbo.Foo(5); -- Error here: too many arguments specified
Fair enough, you might say, but:
SET NOEXEC ON;
GO
CREATE FUNCTION Foo() RETURNS TABLE AS RETURN (SELECT 0 Bar);
GO
SELECT * FROM dbo.Foo(5); -- No error, because Foo doesn't exist
So SET NOEXEC ON can serve neither as a way to guarantee a script is completely valid, nor as a way to determine a script is definitely invalid. As long as you're aware of its limitations it can still be useful.
There is another setting that does work consistently as far as object names go: SET PARSEONLY ON. This not only doesn't execute statements, it doesn't even compile them. The example above which gives an error with NOEXEC will give no error with PARSEONLY. As does the following:
CREATE TYPE MyInt FROM INT;
GO
CREATE TABLE A(ID MyInt);
-- Error under NOEXEC since MyInt does not exist, no error under PARSEONLY
So if all you want to check is syntactical validity, PARSEONLY is better than NOEXEC.