Wednesday, March 28, 2012

If exists

Any idea why
BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
works and not
BEGIN IF EXISTS (select * from mytable) '0' else '1' end
I am trying to put the latter into a case statement.
--
Regards,
JamieYou cannot just use '0' or '1', according to SQL syntax you need a SELECT.
This will work:
IF EXISTS (select * from mytable) SELECT '0' else SELECT '1'
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2C00DCB8-2CEA-45FD-9094-F69D49777DAF@.microsoft.com...
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie|||Hi Jamie
It may be clearer if you indented!!
BEGIN
IF EXISTS (SELECT * FROM mytable)
PRINT '0'
ELSE
PRINT '1'
END
This will work, but you can't use it with a PRINT statement:
BEGIN
SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
END
Although you can do:
BEGIN
DECLARE @.output char(1)
SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
PRINT @.output
END
HTH
John
"thejamie" wrote:

> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie|||John,
I see part of the error of my ways. This is a condition for a case
statement thus
Begin
Select
case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
select 1 end)=0 then
print 'green'
else
print 'red'
end
Still get an error here...
--
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jamie
> It may be clearer if you indented!!
> BEGIN
> IF EXISTS (SELECT * FROM mytable)
> PRINT '0'
> ELSE
> PRINT '1'
> END
> This will work, but you can't use it with a PRINT statement:
> BEGIN
> SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> END
> Although you can do:
> BEGIN
> DECLARE @.output char(1)
> SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> PRINT @.output
> END
> HTH
> John
> "thejamie" wrote:
>|||Seems like some of the logic here is a bit redundant. Maybe you can simplify
like this:
Select
case when exists(Select * from atoms.dbo.tpreallocations) then
'green'
else
'red'
end
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0
> else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
>|||Hi Jamie
You don't need to do the if, Case can take a boolean expression in the WHEN
clause, but you can not use PRINT with a CASE statement or use print within
a
SELECT statement
To use PRINT
BEGIN
DECLARE @.output char(5)
SET @.output = CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
PRINT @.output
END
To use SELECT to return a result set
BEGIN
SELECT CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
END
HTH
John
"thejamie" wrote:
[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 els
e
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
>

No comments:

Post a Comment