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
dastaubAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThomasianCommented: 
DECLARE @year int
SET @year=2009	--start year

DECLARE @DBNameX varchar(10)

WHILE @year < 2012 BEGIN	--end year+1
	SET @DBNameX = 'Year' + CAST(@year as varchar)
	EXEC [dbo].[UpdateReport01] @DBNameX
	EXEC [dbo].[UpdateReport02] @DBNameX
	EXEC [dbo].[UpdateReport03] @DBNameX
	SET @year=@year+1
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bhavesh ShahLead AnalysistCommented:
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

0
dastaubAuthor Commented:
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
0
ThomasianCommented:
If the name of your database is Year2009, Year2010, etc., then try:
WHILE @year < 2015 BEGIN      --end year+1
      SET @DBNameX = 'Year' + CAST(@year as varchar)
      IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @DBNameX) BEGIN
            EXEC [dbo].[UpdateReport01] @DBNameX
            EXEC [dbo].[UpdateReport02] @DBNameX
            EXEC [dbo].[UpdateReport03] @DBNameX
      END
      SET @year=@year+1
END

Open in new window

0
dastaubAuthor Commented:
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')
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.