Help with a CASE statement.

I am trying to get this case statement to work where it will
concatenate the values into a single string.
If I place anything with an equals sign such as (SELECT @.status_message
= @.status_message + 'variable') it give me an error. Any ideas. It
probably obvious but I am failing to see it.
DECLARE @.status_message varchar(100)
Set @.status_message = ''
SELECT * from master..sysdatabases
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
WHEN (status & 4194304) <> 0 THEN 'autoshrink'
WHEN (status & 32768) <> 0 THEN 'emergency mode'
WHEN (status & 4096) <> 0 THEN 'single user'
WHEN (status & 2048) <> 0 THEN 'dbo use only'
WHEN (status & 1024) <> 0 THEN 'read only'
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 256) <> 0 THEN 'not recovered'
WHEN (status & 128) <> 0 THEN 'recovering'
WHEN (status & 64) <> 0 THEN 'pre recovery'
WHEN (status & 32) <> 0 THEN 'loading'
WHEN (status & 16) <> 0 THEN 'torn page detection'
WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE 'Unknown'
end
from master..sysdatabasesYou cannot assign values to variables in the same select statement in which
you also return a result-set to the client.
E.g. this is not allowed:
select <column list>
,@.<variable> = <some column>
from <table>
Put the variable assignment ina separate query, then include it in the one
that returns the result to the client.
select @.<variable> = <some column>
from <table>
select <column list>
,@.<variable> as <variable name>
from <table>
Does that answer your question?
ML
http://milambda.blogspot.com/|||Sort of, I know what the problems is, I am looking for a work around,
or a different solution that might achieve the same results. someone
said that i sould thorugh it into a loop, so i might try that.|||Anyone. I must be totaly brainfried.|||>> I am trying to get this case statement to work where it will concatenate
Which values are you talking about? The string values in the THEN clause of
the CASE? Please elaborate on what you are trying to do here.
What is the error message? Which piece of code are you trying to run to
generate the error?
Anith|||I figured it out
/ ****************************************
*******************
Returns a the STATUS of all databases on a server in English
****************************************
*******************/
SELECT @.@.SERVERNAME AS SERVER, VERSION, LEFT(name,30) AS [Databases],
DBID,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',Aautoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',Select Into / Bulk Copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',Truncate Log on Checkpoint' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',Torn Page Detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',Loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',Pre-Recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',Recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',Not Recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',Offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',Read Only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE Only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',Single User' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',Emergency Mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',Cleanly Shutdown' END,
2,8000) AS OPTIONS_1,
SUBSTRING(CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default'
END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END
+
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor'
END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit'
END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS OPTIONS_2, CMPTLEVEL, FILENAME
FROM master..sysdatabases|||On 27 Feb 2006 14:48:32 -0800, Matthew wrote:

>I am trying to get this case statement to work where it will
>concatenate the values into a single string.
>If I place anything with an equals sign such as (SELECT @.status_message
>= @.status_message + 'variable') it give me an error. Any ideas. It
>probably obvious but I am failing to see it.
>DECLARE @.status_message varchar(100)
>Set @.status_message = ''
>SELECT * from master..sysdatabases
>SELECT name, dbid, status, cmptlevel, filename,
>CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
> WHEN (status & 4194304) <> 0 THEN 'autoshrink'
> WHEN (status & 32768) <> 0 THEN 'emergency mode'
> WHEN (status & 4096) <> 0 THEN 'single user'
> WHEN (status & 2048) <> 0 THEN 'dbo use only'
> WHEN (status & 1024) <> 0 THEN 'read only'
> WHEN (status & 512) <> 0 THEN 'offline'
> WHEN (status & 256) <> 0 THEN 'not recovered'
> WHEN (status & 128) <> 0 THEN 'recovering'
> WHEN (status & 64) <> 0 THEN 'pre recovery'
> WHEN (status & 32) <> 0 THEN 'loading'
> WHEN (status & 16) <> 0 THEN 'torn page detection'
> WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
> WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
> WHEN (status & 1) <> 0 THEN 'autoclose'
> ELSE 'Unknown'
>end
>from master..sysdatabases
Hi Matthew,
Though concatenating these in a single string is presentation and
shouyld therefor be handled in the presentation layer, I'll give you a
working SQL solution:
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
ELSE '' END
+ CASE WHEN (status & 4194304) <> 0 THEN 'autoshrink'
ELSE '' END
+ CASE WHEN (status & 32768) <> 0 THEN 'emergency mode'
ELSE '' END
(...)
+ CASE WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE '' END
from master..sysdatabases
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:81e902hircfvvlacle0bg16ois08j7kmoq@.
4ax.com...
>.
> Though concatenating these in a single string is presentation and
> shouyld therefor be handled in the presentation layer,
>.
Hmm where have I heard this same idea before?
Once upon a time many were saying the same thing about
row numbering or worse line numbering.Surely this was
something to be done on the client whereas it had no
place being done on the server.(Not to mention the situation
was further muddled when the same voices used the very same
constructs as intermediate results in a query).Now along comes
Sql 2005 with some Sql-99 analytic functions like row_number()
and viola row numbering is no longer *presentation* and it's
perfectly fine to do on the server.To keep the analytics
company, 2005 introduces some more xml.Now we learn that
concatenating over rows can be done explicitly with it.
Presentation now has now become transformed into a serve-ice.
Since the underlying nature of 2005 hasn't changed
save for the mechanisms to do these things, it is now
*expedient* to do them on the server.Of course the shift
over even includes the dreaded crosstab with the ingenius
implementation of PIVOT.One must marvel at the ease of how
MS can change presentation to serve-ice:) But this shift may
come with some head scratching.Least some users wonder why
they can't simulate the serve-ice of 2005 to overcome the
presentation inherent in 2000.But one of the great things
about expediency is that it takes so shallow an explanation.
Flip flopping is alive and well in the world of sql.And for those
who actually study the subject academically I would say
't'where ignorance is bliss,t'is folly to be wise':)
$.03 from
www.rac4sql.net|||Well I guess as "proof" in SQL 2005 running the Execution Plan, the
cost associated for running query is exactly the same for both. So I
really boils down, which way is the "more correct" way to code.
e.g. which is better in practice.|||'which is better in practice' is just another way of saying what
is the most expedient way.Whatever works best for you:)
"Matthew" <MKruer@.gmail.com> wrote in message
news:1141230238.399541.6200@.z34g2000cwc.googlegroups.com...
> Well I guess as "proof" in SQL 2005 running the Execution Plan, the
> cost associated for running query is exactly the same for both. So I
> really boils down, which way is the "more correct" way to code.
> e.g. which is better in practice.
>