Sql-server – Apply procedures in multiple databases

master-system-databasesql server 2014stored-procedurest-sql

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

and

USE aDB
exec sp_procedure 'With Arguments'

But it says sp_procedure does not exists…

I'm working with windows SQL server 2014

Help appreciated.

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

Best Answer

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?

If I understood you correct, you need to execute the same procedure in many databases.

For example, it can be a procedure that makes a script of all the indexes of the specific table, in this case the procedure is always the same but it should be "local" respect to database where the table resides, this way you can use sys.indexes/sys.columns that are "database sensitive".

The simplest way to do this is to make your procedure "system" procedure, i.e. it will be created in master database, it will have sp prefix and marked as system, so that it can be called from any database and will always use system tables "local" to the database from which it was called.

To accomplish this you should create your procedure only one time in master database as you did:

USE master
CREATE PROCEDURE sp_procedure AS ...

And mark this procedure as system one:

exec sys.sp_MS_marksystemobject 'sp_procedure'

Noe when you run your code like this

USE aDB
exec sp_procedure 'With Arguments'

You will not get

sp_procedure does not exists

anymore.

........................................................

If you still prefer to create this procedure many times in every database the simplest way to do it is to save your procedure script in a file including the rows

IF EXISTS  Procedure 
THEN DROP
GO

And then launch this script from every database:

sqlcmd -S <ComputerName>\<InstanceName> -d <dbname> -i <MyScript.sql>

(you should add -U MyLogin -P MyPassword if you don't want to use Integrated Security).

You should format this string dynamically in your cursor addind dbname like this:

set @cmd = 'sqlcmd -S <ComputerName>\<InstanceName> -d '+ @db_name + ' -i <MyScript.sql>';

exec master.dbo.xp_cmdshell @cmd

.................................................................

Otherwise, you should put the whole text of your sp (create proc...) in a string variable and execute it in your cursor:

declare @sp varchar(max) = cast(' CREATE PROCEDURE ' as varchar(max)) + '[dbo].[Procedure].....'

declare @sql varchar(max) = 'USE ' + @db_name + @sp
EXECUTE(@sql);

Related Solutions

Db2 – create a stored procedure if it doesnt exist using a ddl in db2

IBM Added The OR REPLACE option in version 7.1

So your code should work like this :

CREATE OR REPLACE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION()
SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION
LANGUAGE SQL 
BEGIN 

......
......
update/select statements
......

END

Sql-server – Insert/Update Stored Procedure

One execution of your script will produce one computer name and a list of one or more monitors. You can pass these to a SQL Server stored procedure, you just have to get the parameter types correct.

The computer name is simplest. With just this the SP definition would look like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100)
as
    ...

I use varchar(100) as an example. You use whatever's right for you. The list of monitors is more difficult because it may contain many values. For this you have to use a table-valued parameter and for that you will have to define a type:

create type MonitorList AS TABLE (
    MonitorName varchar(100)
    );
GO

Now the SP looks like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100),
    @Monitors     MonitorList    READONLY
as
    ...

Now to the body of the SP. If you use the names as the primary key in tables Computer and Monitor the INSERT statements are straightforward. Assuming you have primary keys and foreign keys defined -- you should -- you have to insert in the correct sequence to respect these key definitions i.e. into Computer and Monitor and only then into the mapping table.

insert dbo.Computer (ComputerName)
values (@ComputerName);

The type we defined, and variables created off it, behave just like any other table:

insert dbo.Monitor (MonitorName)
select MonitorName
from @Monitors;

insert Monitor_Computer_Map (ComputerName, MonitorName)
select @ComputerName, MonitorName
from @Monitors;

If your tables have surrogate keys that use IDENTITY, however, you have more work to do. You have to capture these identity values as they are generated in the parent tables and use them in the mapping table. This can be done with the OUPUT clause:

declare @ComputerID table ( ComputerID int);

insert dbo.Computer (ComputerName)
output INSERTED.ID
into @ComputerID
values (@ComputerName);

Do the same thing for monitors and use the local table variables to populate the mapping table.

Of course you want to have appropriate validation, duplicate checking and error handling in the body of the SP, too.

You don't say what scripting language you use. The documentation for it will tell you how to declare and populate stored procedure parameters for SQL Server.

Response to OP's EDIT #2:

First, a few tips. Please post the full error message; it helps immensely with debugging. Second, if you're using SSMS you can double-click an error and it will highlight the code in error. Third, get in the habit of closing your statements with a semicolon. It is not required yet but it will be soon.

If all computers have exactly one monitor then the TVP is not needed. You are correct. How many developers have only one monitor these days? I've seen finance traders' stations with eight. In these cases you do want a TVP. Please, please, please do not be tempted to write @Serial1, @Serial2, @Serial3 ....

Your code will throw the error Must declare the scalar variable "@MonitorId". This is because of your third INSERT statement:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
VALUES (@MonitorId, @ComputerId);

When you use the INSERT..VALUES syntax SQL Server demands that there can be only one value per variable. You have provided table valued variables which could (potentially) hold many values. What you need is the INSERT..SELECT syntax. Yes, I know @ComputerID only has one row but it is a table and it could have many rows. What you need is:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
SELECT m.Id, c.Id
from @ComputerId AS c
cross join @MonitorId AS m;

Best Answer

Related Solutions

Db2 – create a stored procedure if it doesnt exist using a ddl in db2

Sql-server – Insert/Update Stored Procedure

Related Question