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

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

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

Start Free Trial

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