Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL T-SQL

Posted on 2011-10-05
5
Medium Priority
?
238 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:dastaub
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 36922159
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36922708
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
 

Author Comment

by:dastaub
ID: 36922812
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
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 2000 total points
ID: 36922827
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
 

Author Comment

by:dastaub
ID: 36924414
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question