Avatar of dastaub
dastaub
Flag for United States of America asked on

SQL T-SQL

Currently, I execute the below T-SQL that executes 3 SP's on 3 databases.
***************************************
EXEC [dbo].[UpdateReport01] 'Year2009'
EXEC [dbo].[UpdateReport02] 'Year2009'
EXEC [dbo].[UpdateReport03] 'Year2009'

EXEC [dbo].[UpdateReport01] 'Year2010'
EXEC [dbo].[UpdateReport02] 'Year2010'
EXEC [dbo].[UpdateReport03] 'Year2010'

EXEC [dbo].[UpdateReport01] 'Year2011'
EXEC [dbo].[UpdateReport02] 'Year2011'
EXEC [dbo].[UpdateReport03] 'Year2011'
***************************************
Is there a way to create a loop that T-SQL loops thru using the DB names provided?
I assume this will loop 3 times or however many DB names are supplied.

@DBNameX = "Year2009, Year2010, Year2011..."
do until ...
EXEC [dbo].[UpdateReport01] @DBNameX
EXEC [dbo].[UpdateReport02] @DBNameX
EXEC [dbo].[UpdateReport03] @DBNameX
loop
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
dastaub

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Thomasian

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Bhavesh Shah

Hi,

you can use loop as experts showed you.
you can use this way too

Create PROC Proc_UpdateReport 
@DBName VarChar(20)
AS

EXEC [dbo].[UpdateReport01] @DBName
EXEC [dbo].[UpdateReport02] @DBName
EXEC [dbo].[UpdateReport03] @DBName

Open in new window


 Proc_UpdateReport  'Year2010'

Open in new window

dastaub

ASKER
If I loop through 2015, I would then first check if the database existed before firing off the SP's.
Is there an IF statement for a database existing?

WHILE @year < 2015 BEGIN      --end year+1
           if yearXXX exists
           begin
      SET @DBNameX = 'Year' + CAST(@year as varchar)
      EXEC [dbo].[UpdateReport01] @DBNameX
      EXEC [dbo].[UpdateReport02] @DBNameX
      EXEC [dbo].[UpdateReport03] @DBNameX
            end
SET @year=@year+1
END
SOLUTION
Thomasian

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
dastaub

ASKER
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @DBNameX) , I could not get to work, but the below did work.

if exists(select * from sys.databases where name = 'databasename')
Your help has saved me hundreds of hours of internet surfing.
fblack61