SQL dynamically create stored procedures?
dynamic-sql, sql, sql-server-2005, stored-procedures
Solution
I wouldn't call the solution intuitive, but apparently this works. I prefer the look of this one though.
Problem
I am trying to create a script to create/setup a group of stored procedures that will all be fairly similar. So I am trying to loop through this code, changing the `@DATABASE_NAME` and `@TableName` when needed. ``` /* 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 */ ``` But I am getting errors saying 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. or 'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name. All the solutions online suggest using GO, but that won't work in dynamic SQL. Does anyone know a possible solution for SQL Server 2005?