Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IF statment on Server Group/ DB name Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 12:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 3:38 PM
Points: 8, Visits: 41
I have a Server Group and I would like to write an if statement based on the the server registration or database name.

I need a field prefixed with a string and depending on the DB the Prefix will change.

I don't know how to check the Server registration name or the data base name the query is running on.

There are 10 different databases in the server group.
Post #1542878
Posted Wednesday, February 19, 2014 2:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:45 AM
Points: 464, Visits: 457
I'm not sure if I understood your requirement correctly or not but I feel dynamic SQL is what you are looking for ...If yes then something like following should serve as a starting point
DECLARE @Query VARCHAR(8000),	@DBName VARCHAR(1000)

SELECT @DBName = 'SQL_TRAINING'

SET @Query =
'IF (DB_ID(''' + @DBName + ''') > 4 )
BEGIN
PRINT ''' + @DBName + '''
END'

--PRINT @Query

EXEC (@QUERY)

You may want to refer http://www.sommarskog.se/dynamic_sql.html
Post #1542892
Posted Wednesday, February 19, 2014 7:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 3:38 PM
Points: 8, Visits: 41

I don't think that does it? I'm looking for something like this. The first two fail because they think DB_ID is a column and the last one does nothing.

,iif (DB_ID = '13-11-21_Nov_Part_02_C3' ,  '02'+[ID], Null) as HYLC_UniqueItemId_2

using CASE DB_ID() or CASE DB_Name()

,CASE DB_ID() 
WHEN '13-11-21_Nov_Part_01_C3' THEN ('01_'+[ID])
WHEN '13-11-21_Nov_Part_02_C3' THEN ('02_'+[ID])
WHEN '13-11-21_Nov_Part_03_C3' THEN ('03_'+[ID])

WHEN '14-01-17_Jan_Part_04_C5' THEN ('04_'+[ID])
WHEN '14-01-17_Jan_Part_05_C5' THEN ('05_'+[ID])

WHEN '14-02-16_Feb_Part_06_C6' THEN ('06_'+[ID])
WHEN '14-02-16_Feb_Part_07_C6' THEN ('07_'+[ID])
WHEN '14-02-16_Feb_Part_08_C6' THEN ('08_'+[ID])
WHEN '14-02-16_Feb_Part_09_C6' THEN ('09_'+[ID])
WHEN '14-02-16_Feb_Part_10_C6' THEN ('10_'+[ID])
ELSE 'null'
END as UniqueItemId

,iif (DB_Name() = '13-11-21_Nov_Part_01_C3' ,  '01_'+[ID], Null) as UniqueItemId
,iif (DB_Name() = '13-11-21_Nov_Part_02_C3' , '02_'+[ID], Null) as UniqueItemId
,iif (DB_Name() = '13-11-21_Nov_Part_03_C3' , '03_'+[ID], Null) as UniqueItemId

Post #1543306
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse