February 19, 2014 at 12:58 am
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.
February 19, 2014 at 2:20 am
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
February 19, 2014 at 7:07 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply