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
**************************
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
Last Comment
dastaub
ASKER CERTIFIED SOLUTION
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.
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
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
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.
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')
if exists(select * from sys.databases where name = 'databasename')
you can use loop as experts showed you.
you can use this way too
Open in new window
Open in new window