SQL Execute Statement for Create Procedure in multiple databases

I am running a loop which iterates through a bunch of databases and among several commands there is one that installs a procedure. I am having a problem with this. I have tried a few methods but they error.

'use '+@DBName+';
            CREATE proc [dbo].proc_ContributorTapHistoryUpdate ....

Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.

CREATE proc '+@DBName+'.[dbo].proc_ContributorTapHistoryUpdate
Msg 166, Level 15, State 1, Line 2
'CREATE PROCEDURE' does not allow specifying the database name as a prefix to the object name.

Code Sample:
open ProjectList
fetch next from ProjectList
into @DBName
while @@FETCH_STATUS = 0
begin
	execute('CREATE proc '+@DBName+'.[dbo].proc_ContributorTapHistoryUpdate
		as
		begin
			select * from sysobjects where name = ''FU''
		end
	')
	if @@ERROR <> 0
		goto ERR_EXIT
	fetch next from ProjectList
	into @DBName
end
ERR_EXIT:
close ProjectList
deallocate ProjectList

Open in new window

etsellincAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Yup. Try using the GO separator after the USE directive.
0
etsellincAuthor Commented:
'use '+@DBName+' go
            CREATE proc [dbo].proc_ContributorTapHistoryUpdate


I get this error

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
0
ste5anSenior DeveloperCommented:
Your resulting string must look like

USE dbname;
GO
CREATE ..

Open in new window


with the spaces/line brakes.

btw, where do you execute this SQL? What tool do you use?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vikas GargBusiness Intelligence DeveloperCommented:
HI,

You can try this method rather than Cursor.

Actually your need is to create stored procedure for multiple database which can be achieved with this.

DECLARE @SQL VARCHAR(500), @DB VARCHAR(50) , @Text varchar(4000)

SELECT @Text = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''CREATE PROCEDURE YourProc AS <<Your fancy code here>>'') END'
EXEC sp_MSforeachdb @Text

Open in new window

Here you need mention the name of the databases for which you do not wish to create the stored procedure.

Hope this will help you..
0
etsellincAuthor Commented:
' use '+@DBName+';  go
            CREATE proc [dbo].proc_Dumb ......


Same results as before:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
0
etsellincAuthor Commented:
For ste5an -
I still receive the same error.

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
0
etsellincAuthor Commented:
For Vikas Garg -

There are a lot of databases on the server and only specific ones will be updated. The names of the databases are the result of a selection from a table with reconstruction of that name replacing number portions of those DB Names.

Your code would be a possible solution with a little work. I will keep it in mind if no other solution presents itself.

Thanks,
Mark
0
ste5anSenior DeveloperCommented:
A, I see your problem now.. E.g.

DECLARE @Sql VARCHAR(MAX) = '';
DECLARE @Template VARCHAR(MAX) = 'USE ?; EXECUTE (''CREATE PROCEDURE dbo.p_Test_2 AS SELECT -1;'');';

SELECT  @Sql = @Sql + REPLACE(@Template, '?', D.name)
FROM    sys.databases D
WHERE   D.name LIKE 'MyDatabases%';

EXECUTE ( @Sql );

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
etsellincAuthor Commented:
For ste5an,

I didn't think of placing an execute within the execute statement.
I got it to work with some modifications.

Thanks!!!
Mark
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.