Problems with case statement 1

[Kalin]

Hi All,

currently I'm developing a single statement for looking up the status of the different databases in an instance.

The script runs OK but stumbles on the case statement.

Does anyone have a resolution for this ??

Greetz,

Kalin

Use Master
Declare @DatabaseName Varchar(50)
Declare @SQL Varchar(5000)
Declare @status Integer
Declare @NewStatus Integer
Declare @BitString Varchar (500)
Declare @NumOFBits Integer
Declare @Cycle Integer

Declare DBs CURSOR FOR
SELECT name, status
FROM sysdatabases
WHERE name not in ('model', 'Northwind', 'pubs')

OPEN DBs

SET @BitString = ''

WHILE 0=0
BEGIN
FETCH NEXT FROM DBs INTO @DatabaseName, @Status
IF @@fetch_status <> 0 BREAK
SET @NumOfBits = 1
WHILE POWER(2, @NumOfBits) <= @Status
BEGIN
SET @NumOfBits = @NumOfBits + 1
END
SET @BitString = REPLICATE('0', @NumOfBits)
SET @Cycle = @NumOfBits
-- SET @NewStatus = @Status
WHILE @Cycle >= 1
BEGIN
IF (@Status - (POWER(2, @Cycle))) >= 0
BEGIN
SET @BitString = LEFT(@BitString, @NumOFBits - @Cycle) + '1' + RIGHT(@BitString, @Cycle)
SET @Status = @Status - POWER(2, @Cycle)
END
SET @Cycle = @Cycle - 1
END
CASE
WHEN SUBSTRING(@BitString, LEN(@BitString) - 10, 1) = '1' THEN SET @SQL = @SQL + ' UNION SELECT ''' + @Status + ''', ''Offline'' FROM master.dbo.syscolumns'
WHEN SUBSTRING(@BitString, LEN(@BitString) - 8, 1) = '1' THEN SET @SQL = @SQL + ' UNION SELECT ''' + @Status + ''', ''Recovering'' FROM master.dbo.syscolumns'
ELSE SET @SQL = @SQL + ' UNION SELECT top 1 ''' + @DatabaseName + ''', ''Online'' from ' + @DatabaseName + '.dbo.syscolumns'
END
END

CLOSE DBs
DEALLOCATE DBs

 

[LFCfan]

CASE is not a case statement, it's a case expression, used this way:

SET @SQL =
CASE
WHEN <expr1> THEN <sql1>
WHEN <expr2> THEN <sql2>
ELSE <sql3>
END
Hope this helps