How do you return a constant from an sql statement?

Question

How do I return a constant from an sql statement?

For example how would I change the code below so "my message" would return if my (boolean expression) was true

if (my boolean expression)
 "my message"
else
 select top 1 name from people;

I am using ms sql 2000

Answer 1

Did you try:

select 'my message';

Answer 2

select "my message" as message

Answer 3

I don't have MSSQL handy, but check the syntax for the CASE statement in case I got it wrong and also I'm not sure if the TOP 1 should go outside the case as I put it here or if it should go inside (ELSE TOP 1 name). The idea is:

SELECT TOP 1 CASE WHEN myexpression = 'true' THEN 'my message' ELSE name END
FROM people;

Here myexpression has to be either constants or related to the tables present in the query, for example

CASE WHEN address LIKE '%Michigan%'

where address is another field in the table people.

PS: Found the MSSQL CASE syntax here :-)

Answer 4

select top 1 name 
from people
where @MyParameter = whatever

union

select 'my message' as name
where @MyParameter != whatever

All in one statement.

Answer 5

I just tried this on the AdventureWorks database and it works

Use AdventureWorks

Declare @myVar int
SET @myVar = 1

if (@myVar = 2)

     select top 2 * from HumanResources.Department

else

     select top 1 * from HumanResources.Department