I have reach a support line that gave me a T-SQL Script for one DB that I now need to apply many more times.
The original script goes like:
IF EXISTS Procedure THEN DROP GO CREATE PROCEDURE [dbo].[Procedure] ... END GO exec Procedure 'With Arguments'
What I'm trying to do goes like:
DECLARE @db_name VARCHAR(50) -- database name DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases where create_date > DATETIMEFROMPARTS(2017,12,27,0,0,0,0) AND create_date < DATETIMEFROMPARTS(2017,12,30,0,0,0,0) OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE('USE ' + @db_name) exec Procedure 'With Arguments' FETCH NEXT FROM db_cursor INTO @db_name END CLOSE db_cursor DEALLOCATE db_cursor
But I' m not getting the expected results. I believe that is because I'm not creating the procedure inside each DB. What is the clean way to do the "If Exists" and "Create Procedure" repeatedly?
I also used :
USE master CREATE PROCEDURE sp_procedure AS ...
USE aDB exec sp_procedure 'With Arguments'
But it says sp_procedure does not exists...
I'm working with windows SQL server 2014
EDIT: The end code after reading all the answers and marking as System was:
... BEGIN SET @CMD = 'exec ' + @db_name + '.dbo.sp_procedure ''With Arguments'';'; exec sp_executesql @CMD; ...