<p>As part of my integration strategy, I have a few SQL scripts that run in order to update the database. The first thing all of these scripts do is check to see if they need to run, e.g.:</p>

<pre><code>if @version &lt;&gt; @expects
    begin
        declare @error varchar(100);
        set @error = 'Invalid version. Your version is ' + convert(varchar, @version) + '. This script expects version ' + convert(varchar, @expects) + '.';
        raiserror(@error, 10, 1);
    end
else
    begin
        ...sql statements here...
    end
</code></pre>

<p>Works great! Except if I need to add a stored procedure. The "create proc" command must be the only command in a batch of sql commands. Putting a "create proc" in my IF statement causes this error:</p>

<pre>
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
</pre>

<p>Ouch! How do I put the CREATE PROC command in my script, and have it only execute if it needs to?</p>

[{'answer_id': 55546, 'author': 'Josh Hinman', 'author_id': 2527, 'author_profile': 'https://Stackoverflow.com/users/2527', 'pm_score': 6, 'selected': True, 'text': "<p>Here's what I came up with:</p>\n\n<p>Wrap it in an EXEC(), like so:</p>\n\n<pre><code>if @version &lt;&gt; @expects\n    begin\n        ...snip...\n    end\nelse\n    begin\n        exec('CREATE PROC MyProc AS SELECT ''Victory!''');\n    end\n</code></pre>\n\n<p>Works like a charm!</p>\n"}, {'answer_id': 55773, 'author': 'Anthony K', 'author_id': 1682, 'author_profile': 'https://Stackoverflow.com/users/1682', 'pm_score': 3, 'selected': False, 'text': '<p>But watch out for single quotes within your Stored Procedure - they need to be "escaped" by adding a second one.  The first answer has done this, but just in case you missed it.  A trap for young players.</p>\n'}, {'answer_id': 55806, 'author': 'Peter', 'author_id': 5189, 'author_profile': 'https://Stackoverflow.com/users/5189', 'pm_score': 2, 'selected': False, 'text': '<p>Versioning your database is the way to go, but...  Why conditionally create stored procedures.  For Views, stored procedures, functions, just conditionally drop them and re-create them every time. If you conditionally create, then you will not clean-up databases that have a problem or a hack that got put in 2 years ago by another developer (you or I would never do this) who was sure he would remember to remove the one time emergency update.</p>\n'}, {'answer_id': 56002, 'author': 'robsoft', 'author_id': 3897, 'author_profile': 'https://Stackoverflow.com/users/3897', 'pm_score': 1, 'selected': False, 'text': "<p>I must admit, I would normally agree with @Peter - I conditionally drop and then unconditionally recreate every time. I've been caught out too many times in the past when trying to second-guess the schema differences between databases, with or without any form of version control.</p>\n\n<p>Having said that, your own suggestion @Josh is pretty cool. Certainly interesting. :-)</p>\n"}, {'answer_id': 350029, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p>Problem with dropping and creating is you lose any security grants that had previously been applied to the object being dropped. </p>\n'}, {'answer_id': 441970, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<pre><code>IF NOT EXISTS(SELECT * FROM sys.procedures WHERE name = 'pr_MyStoredProc')\nBEGIN\n\n     CREATE PROCEDURE pr_MyStoredProc AS .....\n     SET NOCOUNT ON\nEND\n\nALTER PROC pr_MyStoredProc\nAS\nSELECT * FROM tb_MyTable\n</code></pre>\n"}, {'answer_id': 441972, 'author': 'Jobo', 'author_id': 51915, 'author_profile': 'https://Stackoverflow.com/users/51915', 'pm_score': 0, 'selected': False, 'text': "<p>use the 'Exists' command in T-SQL to see if the stored proc exists. If it does, use 'Alter', else use 'Create'</p>\n"}, {'answer_id': 9383336, 'author': 'DWalker59', 'author_id': 1224123, 'author_profile': 'https://Stackoverflow.com/users/1224123', 'pm_score': 2, 'selected': False, 'text': "<p>This is an old thread, but Jobo is incorrect:  Create Procedure must be the first statement in a batch.  Therefore, you can't use <code>Exists</code> to test for existence and then use either <code>Create</code> or <code>Alter</code>.  Pity.</p>\n"}, {'answer_id': 36646576, 'author': 'Proggear', 'author_id': 5247175, 'author_profile': 'https://Stackoverflow.com/users/5247175', 'pm_score': 3, 'selected': False, 'text': '<p>SET NOEXEC ON is good way to switch off some part of code</p>\n\n<pre><code>IF NOT EXISTS (SELECT * FROM sys.assemblies WHERE name = \'SQL_CLR_Functions\')\n  SET NOEXEC ON\nGO\nCREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N\',\')\nRETURNS TABLE (str nvarchar(4000)) AS EXTERNAL NAME SQL_CLR_Functions.[Granite.SQL.CLR.Functions].CLR_CharList_Split\nGO\nSET NOEXEC OFF\n</code></pre>\n\n<p>Found here:\n<a href="https://codereview.stackexchange.com/questions/10490/conditional-create-must-be-the-only-statement-in-the-batch">https://codereview.stackexchange.com/questions/10490/conditional-create-must-be-the-only-statement-in-the-batch</a></p>\n\n<p>P.S. Another way is SET PARSEONLY { ON | OFF }. </p>\n'}, {'answer_id': 49648591, 'author': 'Dave Thompson', 'author_id': 1049108, 'author_profile': 'https://Stackoverflow.com/users/1049108', 'pm_score': 1, 'selected': False, 'text': "<p>My solution is to check if the proc exists, if so then drop it, and then create the proc (same answer as @robsoft but with an example...)</p>\n\n<pre><code>IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'PROC_NAME' AND xtype='P') \nBEGIN\n    DROP PROCEDURE PROC_NAME\nEND\nGO\nCREATE PROCEDURE PROC_NAME\n    @value  int\nAS\nBEGIN\n    UPDATE  SomeTable\n    SET     SomeColumn = 1\n    WHERE   Value = @value\nEND\nGO\n</code></pre>\n"}, {'answer_id': 57115083, 'author': 'Display name', 'author_id': 4501035, 'author_profile': 'https://Stackoverflow.com/users/4501035', 'pm_score': 2, 'selected': False, 'text': "<p>It is much better to alter an existing stored proc because of the potential for properties and permissions that have been added AND which will be lost if the stored proc is dropped.</p>\n\n<p>So, test to see if it NOT EXISTS, if it does not then create a dummy proc. Then after that use an alter statement.</p>\n\n<pre><code>IF NOT EXISTS(SELECT * FROM sysobjects WHERE Name = 'YOUR_STORED_PROC_NAME' AND xtype='P')\nEXECUTE('CREATE PROC [dbo].[YOUR_STORED_PROC_NAME] as BEGIN select 0 END')\nGO\nALTER  PROC [dbo].[YOUR_STORED_PROC_NAME]\n....\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/55506', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2527/']