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,
Jamie
You 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 else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
|||I read that WHEN EXISTS... is faster than the following logic:
CASE (select count(*) from atoms.dbo.tpreallocations tp with(nolock) join
atoms.dbo.torderlines tol with(nolock) on tp.orderlinekey = tol.orderlinekey
where isnull(packstatus,0) = 0 and tol.bol = tbols.bolid)
WHEN 0 THEN ...
http://www.sql-server-performance.com/transact_sql.asp
Hoping this is correct - am trying to improve performance of the view.
Thanks for help from you both.
Regards,
Jamie
"Plamen Ratchev" 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...
>
>
|||>I read that WHEN EXISTS... is faster than the following logic:
Just because you read it somewhere, doesn't always make it true.

> CASE (select count(*) from atoms.dbo.tpreallocations tp with(nolock) join
> atoms.dbo.torderlines tol with(nolock) on tp.orderlinekey =
> tol.orderlinekey
> where isnull(packstatus,0) = 0 and tol.bol = tbols.bolid)
> WHEN 0 THEN ...
> http://www.sql-server-performance.com/transact_sql.asp
> Hoping this is correct - am trying to improve performance of the view.
Don't hope - test and verify. BTW, the link correctly indicates that EXISTS
**may** be more efficient.
In general, your entire approach to addressing the issue within this thread
isn't helping you. You continue to dribble out information, bit by bit.
Trying to help you with problems involving the syntax you are using, without
knowing exactly how you are using it, is difficult at best. It is likely to
lead to inappropriate suggestions. You started asking about syntax, but the
real issue seems to be performance. The most correct syntax in the world
will not necessarily make your query more efficient. No offense, but
trouble with relatively basic syntax likely means you are in over your head
at this point. Perhaps you should consider some additional expertise for
help with the tuning/management problems you seem to be experiencing.
Improving the query involves looking at the entire query and examining all
aspects of the logic and data to ensure that both the database and the query
have been optimized. Optimization is a two-way street. Often, there are
performance tradeoffs that must be made to provide an overall level of
performance for the entire system. Tuning one particular query to the
maximum degree possible can have a detrimental effect on other aspects of
the system. In general, you shouldn't need locking hints. You should use
them only if you understand the locking architecture of sql server and how
the hints affect the overall system as a whole.
|||The help works fine. Testing shows no measurable difference. Thought maybe
I missed something but thanks for your reply.
Regards,
Jamie
"Scott Morris" wrote:

> Just because you read it somewhere, doesn't always make it true.
>
> Don't hope - test and verify. BTW, the link correctly indicates that EXISTS
> **may** be more efficient.
> In general, your entire approach to addressing the issue within this thread
> isn't helping you. You continue to dribble out information, bit by bit.
> Trying to help you with problems involving the syntax you are using, without
> knowing exactly how you are using it, is difficult at best. It is likely to
> lead to inappropriate suggestions. You started asking about syntax, but the
> real issue seems to be performance. The most correct syntax in the world
> will not necessarily make your query more efficient. No offense, but
> trouble with relatively basic syntax likely means you are in over your head
> at this point. Perhaps you should consider some additional expertise for
> help with the tuning/management problems you seem to be experiencing.
> Improving the query involves looking at the entire query and examining all
> aspects of the logic and data to ensure that both the database and the query
> have been optimized. Optimization is a two-way street. Often, there are
> performance tradeoffs that must be made to provide an overall level of
> performance for the entire system. Tuning one particular query to the
> maximum degree possible can have a detrimental effect on other aspects of
> the system. In general, you shouldn't need locking hints. You should use
> them only if you understand the locking architecture of sql server and how
> the hints affect the overall system as a whole.
>
>
|||Hi Jamie
You are probably not testing on significant numbers of records, the EXISTS
clause is usually more scalable. As per Scotts reply, without the whole
picture it is difficult to know if you are looking at the best solution
anyhow. You may also want to read http://www.aspfaq.com/etiquette.asp?id=5006
which gives you some guidelines on posting.
John
"thejamie" wrote:
[vbcol=seagreen]
> The help works fine. Testing shows no measurable difference. Thought maybe
> I missed something but thanks for your reply.
> --
> Regards,
> Jamie
>
> "Scott Morris" wrote: