asked on

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

ste5an

Yup. Try using the GO separator after the USE directive.

etsellinc

ASKER

'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.

ste5an

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?

Vikas Garg

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..

etsellinc

ASKER

' 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.

etsellinc

ASKER

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.

etsellinc

ASKER

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

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

etsellinc

ASKER

For ste5an,

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

Thanks!!!
Mark