title: SQL dynamically create stored procedures?
body: <p>I am trying to create a script to create/setup a group of stored procedures that will all be fairly similar.</p>
  
  <p>So I am trying to loop through this code, changing the <code>@DATABASE_NAME</code> and <code>@TableName</code> when needed. </p>
  
  <pre><code>/* Start loop */
      DECLARE @create_stored_procedure nvarchar(max) 
      SET @create_stored_procedure = N'
         USE [' + @DATABASE_NAME + ']
         CREATE PROCEDURE [dbo].[sproc_imp_' + @TableName + ']
         AS
         BEGIN
             PRINT(''doing something'')
         END'
      EXEC sp_executesql @statement = @create_stored_procedure
  /* End loop */
  </code></pre>
  
  <p>But I am getting errors saying </p>
  
  <blockquote>
    <p>'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.</p>
  </blockquote>
  
  <p>or</p>
  
  <blockquote>
    <p>'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.</p>
  </blockquote>
  
  <p>All the solutions online suggest using GO, but that won't work in dynamic SQL. </p>
  
  <p>Does anyone know a possible solution for SQL Server 2005?</p>