If Nothing Selected Into Variable What Is The Value Of Variable?

DECLARE @.PayTypeValue numeric (18, 5)
SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)
IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?
NULL OR 0DECLARE @.PayTypeValue numeric (18, 5)

SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)

IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?

NULL OR 0

why don't you try it and see for yourself:

SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue|||Also, try
SET @.PayTypeValue = 42
SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue

and see what happens.|||i thought it would return NULL.....|||But it returns...

The answer would be useful for people with similar questions ;)|||The answer is 42.
If the query returns no rows, the variable keeps its old value.|||Ahh that's clever - kinda like having an "else" value.
Cheers ivon|||hmmm...

That could be dangerous if you were using that variable in a loop.
I would wrap the SQL in ISNULL()

set @.somevariable=ISNULL((select somevalue from sometable),-9999999)
If @.somevariable=-9999999
'no rows were returned
Else|||That could be dangerous if you were using that variable in a loop.

How so? It retained it's initially declared value...
If you looped round, @.PayTypeValue would equal 42 each time..?|||Yes, but if you were expecting the value to change with each iteration...
What is returned from the select statement is also dependant on
@.compdiv and @.loadpay, so if those values change, it's quite possilbe that @.paytypevalue would change, too.|||Yes, but if you were expecting the value to change with each iteration...
That's how I found out; I got some very peculiar results.

BTW using IsNull() won't help: the query returns no rows, so there is no null value to replace with something else.

Solutions I'm using are
- Setting the variable to a default value before filling it with the query,
- Checking @.@.ROWCOUNT to see if any rows were returned from the query.|||I think it all depends on how you put it together.

declare @.tmp integer
set @.tmp=42
set @.tmp=isnull((select 18 where 1=0),0)
select @.tmp

Returns 0, which is what I'd expect, since
SELECT 18 WHERE 1=0
returns no rows|||How do I use @.@.ROWCOUNT to do this?

I haven't set this variable to anything before this and at least this instance doesn't loop. This SP is called once per order and checks 14 different things. Then feeds the results into a verification table. Those results and then selected by Crystal Reports and displayed to the user for an entire batch of orders one at a time by order number.|||RedNeckGeek: I now see that you put the isnull around the entire query.

tdecker81:
Something like
SELECT @.l_var = value
FROM table
WHERE <conditions>

SELECT @.l_records = @.@.ROWCOUNT

IF @.l_records > 0
BEGIN
' Do stuff with @.l_var
END|||Interesting. Since I rarely use the first syntax, I was unaware of this behavior:
set nocount on

declare @.MyValue int
set @.MyValue = 42

--This method retains @.MyValue
select @.MyValue = id from sysobjects where 1 = 0
select @.MyValue as 'Unchanged!'

--This method sets @.MyValue to NULL
set @.MyValue = (select id from sysobjects where 1 = 0)
select @.MyValue as 'Set to NULL!'

Learn something new every day...|||Learn something new every day...

my problem is I forget 2 somethings every day, so I think I'm losing the battle...

(most of it is stuff my wife tells me so I guess it's ok)

:)|||I'm sure you're wife will be there to constantly remind you too.sql

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
Regards,
Jamie
On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.
|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
[vbcol=seagreen]
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
--
Regards,
JamieOn Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching th
e
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when i
t
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
--
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 o
r
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching t
he
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when
it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially doe
s
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does i
t
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that ther
e
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:

>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)[vbcol=seagreen]
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
--
Regards,
JamieOn Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
--
Regards,
Jamie
"Hugo Kornelis" wrote:
> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
> >Curious about the above:
> >
> >If there is a query that says
> >If not exists(select * from table)
> >versus one that says
> >If not exists(select * from table with(NoLOCK))
> >
> >Is the second statement more correct than the first (considering that there
> >may be hundreds of hits to that table in a very short space of time -
> >possible 100's per second)?
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:
>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
>> Curious about the above:
>> If there is a query that says
>> If not exists(select * from table)
>> versus one that says
>> If not exists(select * from table with(NoLOCK))
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

IF NOT EXISTS in Oracle?

is oracle capable of doing this?
IF NOT EXISTS( SELECT item_id from PROJECT_WMS_TEMP where item_id = ITEM_ID) THEN
BUILD_PROJECT_WMS_TEMP;
End If;
Thanks
ChristianHello,

you can use PL/SQL Funktion in a SQL statement

something like ...

BEGIN
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
call proc;
END;

Hope this help ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

*** did you ever use Oracle, MySQL, Interbase and Birdstep with one tool ? No ? Try AlligatorSQL ... ***sql

If Is Null in Select Statement

Greetings,
I am getting the following error
"Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IF'."
My SQL statement is:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
dbo.qry1.col5 END
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
Note: qry1 and qry2 are the same query but am join different columns to the
same column in the table
Thanks for the help
KeithSELECT ..., COALESCE(qry1.col5, qry2.col3)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||IF IS NULL doesnt exist in SQL Server. Use ISNULL(Columtocheck, ElseValue),
OR COALESCE(Columntocheck[,ColumnTocheck], ElseValue). If you wanna put an
IF / CAse Expression in your query refer to the BOL and to the syntax of
CASE, example:
CASE Somecolumn
WHEN NULL THEN 'SomeValue'[Or a cloumn]
WHEN ...
...
ELSE 'Something'
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith" <Keith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||If is a Transact SQL Control flow statement, and cannot be used inside of a
SQL Statement. What you want is the SQL Case Expression. (look it up in
Books OnLIne)
as Folows:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
Case When dbo.qry1.col5 Is Null
Then dbo.qry2.col3
Else dbo.qry1.col5 End
FROM dbo.tbl1
LEFT OUTER JOIN dbo.qry2
ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN dbo.qry1
ON dbo.tbl1.col4 = dbo.qry1.col5
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith|||Hi Keith,
The query can be re-written as
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
ISNULL(dbo.qry1.col5, dbo.qry2.col3)
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
best Regards,
Chandra
---
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith

IF inside of CASE

Can anyone help me with condition inside CASE. Below is my code and I get
syntax error at IF line. Thanks.
SELECT TOP 100 PERCENT
dbo.WorkerDeductions.EmployeeNumber,
dbo.WorkerDeductions.DedCode,
dbo.WorkerDeductions.DedAmt,
dbo.WorkerDeductions.DeductionBalance,
dbo.WorkerDeductions.DedPercent,
dbo.PayInfoNHS.EarnGross,
dbo.PayInfoNHS.CheckID,
DedCalc = CASE
WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
BEGIN
dbo.WorkerDeductions.DedAmt
END
ELSE dbo.WorkerDeductions.DeductionBalance
WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
ELSE dbo.WorkerDeductions.DedAmt
END
FROM dbo.WorkerDeductions INNER JOIN
dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
dbo.PayInfoNHS.EmployeeNumber INNER JOIN
dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
dbo.DeductionCodeLookup.DedCode
WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
102))
ORDER BY dbo.WorkerDeductions.EmployeeNumberYou can't use IF in a query; IF is for flow, not statement-level control.
Try a nested CASE:
CASE
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
ELSE
..
END
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ODfTYAyLFHA.2384@.tk2msftngp13.phx.gbl...
> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>|||David C,
You can nest a CASE expression inside another, but you can not use IF inside
a CASE.
AMB
"David C" wrote:

> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>
>|||That worked. Thank you.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

IF in a Select statement

Hi,

What would be the best way to do this, I have a table like so:

JobID Estimate1 Estimate2
 1 20.00 25.00
 2 52.12 47.31
 3 45.00 34.00
 4 60.00 65.00

and I need to create an SQL query that will grab the Sum of the highest estimates, ie (25.00 + 52.12 + 45.00 + 65.00)

I was hoping to do something similar to the SUMIF function in excel:
SELECT SUMIF(Estimate1>=Estimate2, Estimate1, Estimate2) FROM Estimates
but I haven't seen anything simular to that in the documetation.You should be able to use aCASE statement to accomplish this.

Terrisql

IF EXISTS statement in my perl program

I am having trouble finishing my query.

This is what I have:

IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})

I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.

Any suggestions would be appreciated.

Thanks,
LauraUse a SELECT statement instead of the VALUES clause.

-PatP|||And using a select statement will still insert the values into the table?|||In an INSERT statement, you can use the VALUES clause for a list of constants, or a SELECT clause for a list of expressions. The SELECT can include multiple rows and/or contain UNION operators to create multiple row inserts using just one INSERT statement. The SELECT buys you the ability to use expressions (including function calls), generate multiple rows, etc.

-PatP|||Ok I think I got it. Off the subject, can the IF...ELSE Contain an embedded IF...ELSE? Is it ok to have two inserts with the if section?

For example,

IF NOT EXISTS (Select ApplicationID from Application Where (Application = '$_' ))
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')

ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";

Thanks for your help.
-Laura|||I think what you meant was:IF NOT EXISTS (Select ApplicationID
FROM Application Where (Application = '$_' ))
BEGIN
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')
END
ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";Note the addition of the BEGIN...END (in red) to your code.

-PatP|||CROSS JOIN?

And what's with the double quote on the end?|||Ok, so the cross join isn't the way that I'd approach it, but it would work... Kind of like the way you had to construct joins using the pre-SQL-89 syntax. Ugly, but adequate to the job!

The double quote hanging off of the end is actually because Laura is taking this SQL out of the middle of her Perl code. It isn't really part of the SQL syntax at all.

-PatP|||Really...OK

Laura...start writting stored procedures and execute them instead....

Never did learn Pearl...though we did use it for an Oracle project once...

I gotta find a Rexx interpreter for Windoze....|||Originally, I used a left join but when I tested my query in sql server's enterprise manager it automatically changed it to cross join.

The begin and end worked. Thanks for the help.

-Laura|||I gotta find a Rexx interpreter for Windoze....I've never tried it, but I've heard that Reginald (http://www.borg.com/~jglatt/rexx/win32/rxusrw32.htm) isn't too bad.

-PatP|||Yeah, I am pretty new at SQL Server. Just started learning it last month because I am going to be the database administrator. So, I have not begun using stored procedures yet, but I will.

Thanks,
Laura|||Hold the phone...Enterprise Manager?

Do you mean Query Analyzer?

And Pat....RxSocks....Not that I'll find a practical application for it...(Well maybe I could replace DTS), but I bet I can get it to talk to SQL Server...

Very cool

Thanks|||Enterprise Manager -> Opened the table view to see the records that were stored -> on the top toolbar there is an sql button that I tested my statements. It probably isn't the best way, but I wanted to have quick access to my table design and query results.

I am aware of Query Analyzer but I have not used it much.

-Laurasql

if exists help

Hi There,
What is the problem here, I can not find out, it always execute sql instead
of printing 'no'
if exists (select * from dbo.sysobjects where id = object_id('dividend') )
if exists (select syscolumns.name
from sysobjects inner join
syscolumns on
sysobjects.id = syscolumns.id
where sysobjects.name = 'member' and syscolumns.name = 'adgdr1' )
begin
SELECT 'ADMINSYS_DN_AVC' as [SchemeName], MEMBER.[PLAN],
DIVIDEND.DIVIDREF, DIVIDEND.AMOUNT, DIVIDEND.TAX_RELIEF, DIVIDEND.SECURITYRF
,
DIVIDEND.TAX_REL_PD,
DIVIDEND.TAXTRAN
FROM MEMBER INNER JOIN
DIVIDEND ON MEMBER.[PLAN] = DIVIDEND.[PLAN]
WHERE (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN = 0) AND
(MEMBER.MigrationFlag = 'C') OR
(DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN IS
NULL) AND (MEMBER.MigrationFlag = 'C')
end
else
print 'no'
if write something like this it works fine
if exists (select * from dbo.sysobjects where id = object_id('dividend') )
if exists (select syscolumns.name
from sysobjects inner join
syscolumns on
sysobjects.id = syscolumns.id
where sysobjects.name = 'member' and syscolumns.name = 'addkr1' )
print 'yes'
else
print 'no'
Thanks
GaneshGanesh wrote:
> Hi There,
> What is the problem here, I can not find out, it always execute sql
> instead of printing 'no'
>
> if exists (select * from dbo.sysobjects where id =
> object_id('dividend') )
> if exists (select syscolumns.name
> from sysobjects inner join
> syscolumns on
> sysobjects.id = syscolumns.id
> where sysobjects.name = 'member' and syscolumns.name = 'adgdr1' )
> begin
> SELECT 'ADMINSYS_DN_AVC' as [SchemeName], MEMBER.[PLAN],
> DIVIDEND.DIVIDREF, DIVIDEND.AMOUNT, DIVIDEND.TAX_RELIEF,
> DIVIDEND.SECURITYRF, DIVIDEND.TAX_REL_PD,
> DIVIDEND.TAXTRAN
> FROM MEMBER INNER JOIN
> DIVIDEND ON MEMBER.[PLAN] = DIVIDEND.[PLAN]
> WHERE (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN = 0) AND
> (MEMBER.MigrationFlag = 'C') OR
> (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN
> IS NULL) AND (MEMBER.MigrationFlag = 'C')
> end
> else
> print 'no'
>
> if write something like this it works fine
>
> if exists (select * from dbo.sysobjects where id =
> object_id('dividend') )
> if exists (select syscolumns.name
> from sysobjects inner join
> syscolumns on
> sysobjects.id = syscolumns.id
> where sysobjects.name = 'member' and syscolumns.name = 'addkr1' )
> print 'yes'
> else
> print 'no'
Can you explain what you want the script to do so we can validate the
code you posted?
David Gugick - SQL Server MVP
Quest Software|||I have to guess a bit here.
Change the beginning of your procedure to this:

> if exists (select * from dbo.sysobjects where id = object_id('dividend') )
AND exists (select syscolumns.name ...
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:99A82BDA-F5B5-40DC-8C18-434D6B9C2665@.microsoft.com...
> Hi There,
> What is the problem here, I can not find out, it always execute sql
> instead
> of printing 'no'
>
> if exists (select * from dbo.sysobjects where id = object_id('dividend') )
> if exists (select syscolumns.name
> from sysobjects inner join
> syscolumns on
> sysobjects.id = syscolumns.id
> where sysobjects.name = 'member' and syscolumns.name = 'adgdr1' )
> begin
> SELECT 'ADMINSYS_DN_AVC' as [SchemeName], MEMBER.[PLAN],
> DIVIDEND.DIVIDREF, DIVIDEND.AMOUNT, DIVIDEND.TAX_RELIEF,
> DIVIDEND.SECURITYRF,
> DIVIDEND.TAX_REL_PD,
> DIVIDEND.TAXTRAN
> FROM MEMBER INNER JOIN
> DIVIDEND ON MEMBER.[PLAN] = DIVIDEND.[PLAN]
> WHERE (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN = 0) AND
> (MEMBER.MigrationFlag = 'C') OR
> (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN IS
> NULL) AND (MEMBER.MigrationFlag = 'C')
> end
> else
> print 'no'
>
> if write something like this it works fine
>
> if exists (select * from dbo.sysobjects where id = object_id('dividend') )
> if exists (select syscolumns.name
> from sysobjects inner join
> syscolumns on
> sysobjects.id = syscolumns.id
> where sysobjects.name = 'member' and syscolumns.name = 'addkr1' )
> print 'yes'
> else
> print 'no'
>
> --
> Thanks
> Ganesh|||Basically,
I want to execute a select statement based on the table and field existence
for each database
/* Powered by General SQL Parser at www.sqlparser.com */
IF EXISTS (SELECT *
FROM DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('dividend'))
AND EXISTS (SELECT SYSCOLUMNS.NAME
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS
ON SYSOBJECTS.ID = SYSCOLUMNS.ID
WHERE SYSOBJECTS.NAME = 'member'
AND SYSCOLUMNS.NAME = 'adgdr1')
BEGIN
SELECT 'ADMINSYS_DN_AVC' AS [SchemeName],
MEMBER.[PLAN],
DIVIDEND.DIVIDREF,
DIVIDEND.AMOUNT,
DIVIDEND.TAX_RELIEF,
DIVIDEND.SECURITYRF,
DIVIDEND.TAX_REL_PD,
DIVIDEND.TAXTRAN
FROM MEMBER
INNER JOIN DIVIDEND
ON MEMBER.[PLAN] = DIVIDEND.[PLAN]
WHERE (DIVIDEND.TAX_RELIEF <> 0)
AND (DIVIDEND.TAXTRAN = 0)
AND (MEMBER.MIGRATIONFLAG = 'C')
OR (DIVIDEND.TAX_RELIEF <> 0)
AND (DIVIDEND.TAXTRAN IS NULL )
AND (MEMBER.MIGRATIONFLAG = 'C')
END
ELSE
PRINT 'no'
Thanks
Ganesh
"Raymond D'Anjou" wrote:

> I have to guess a bit here.
> Change the beginning of your procedure to this:
>
> AND exists (select syscolumns.name ...
> "Ganesh" <gsganesh@.yahoo.com> wrote in message
> news:99A82BDA-F5B5-40DC-8C18-434D6B9C2665@.microsoft.com...
>
>

If exists from within a select statement.

I have a simple SQL statement, I need to populate columns from several
tables.
I need to populate some of the columns depending if there is data in a
child table that matched the parent table (a one to many relationship).
How can I put in an "if exists" into a select statement?
I need results that will be
file_a.first, file_a.second, file_b.first (when file_a.third = "y"),
file_b.first (when file_a.fourth = "y") ..
so kinda like :
select file_a.first, file_a.second,
if file_a.third = "y" then select file_b.first from file_b ..
if file_a.fourth= "y" then select file_b.first from file_b ..You can=B4t use specify dynnamic resultssets until you use dynmaic SQL
to display the columns.
http://www.sommarskog.se/dynamic_sql.html
Without dynamic SQL you could display the columns but fill them in
conditionally:
SELECT
OneTable.SomeColumn0
CASE OneTable.SomeColumn0 WHEN 'Y' THEN OtherTable.SomeColumn0 END...
But assuming that fileA is your parent table which attributes from
which row do you want to display ? If there are two rows returned for
one parent row, your query like the above one wouldn=B4t be reliable.
Perhaps it would be better to post your DDL and sample data that our
thoughts would=B4nt bw based on assumptions:
http://www.aspfaq.com/5006
HTH, jens Suessmeyer.sql

if exists DROP PROCEDURE/VIEW

how can I drop a propcedure or a view without error for MS SQL 2000/2500

for a table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]

and for a procedure

IF EXISTS ?
DROP PROCEDURE [dbo].[SP_myTABLE_Count]

thank you for helpingPlease excuse me, but I find the number of people who can't use google abolutely unbelievable. A search for sys.objects gives the answer, and the type is P for TSQL stored procedures.

However, keep in mind that SQL Server 2005 hides objects on which you have no access, so even if you query sys.objects first you may get an error, and you should handle that situation.|||please excuse me, but a forum is not google|||please excuse me, but a forum is not google
I am perfectly aware of that. What I tried to point out is that very simple questions, like this one, can very easily be solved by searching google. And it's even way faster. Even better, by eliminating a few of the simpler questions, the users of the forum can use their time trying to resolve a bit tougher issues, for which there is NOT an obvious solution when searching google. I do think you get my point.

By all means, do not think of my post as a personal critism, rather a good guideline for the future. My approach is alway as this: Try to solve, search with google, post on forum.|||actually u need not think that hard to get the "if exists" script. sql server can do it for u. right click on any SP from EM and select "generate SQL Script" or from QA Object-Browser select "create drop script" by right clicking on a SP. check the code generated.....

if exists condition..

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?

regardsfireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?

Whichever you fancy. I would probably write the second nine times out
of ten, but that it would only be because db_id() would not come in
my mind. And a nice characteristic of the first, is that it works on
SQL 2000 as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I would probably write the second
[...]

thank you.
so you would say, first solution is not less professional at all then the
second one.. - right?|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I would probably write the second

[...]
>
thank you.
so you would say, first solution is not less professional at all then the
second one.. - right?

Yes, both are perfectly OK, and which you pick is a matter of taste or
the flux of the moment.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The first one; its more portable between versions of SQL Server, the second
only works in SQL 2005 and upwards.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"fireball" <fireball@.onet.kropka.euwrote in message
news:embrdl$n8d$1@.atlantis.news.tpi.pl...

Quote:

Originally Posted by

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?
>
>
>
>
regards
>
>

If Exists Column ?

Hello

How do you check if a column exist ?

for a table :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO

but I dont find it for a column

Thank youif exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
drop table [dbo].[myTable]|||I want to drop the column not the table ?

thank you for helping|||Here we go.......
--alter table tablename drop column columnname

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
alter table tablename drop column columnname
go|||This is one way....

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 varchar(25))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c'
GO

SELECT * FROM myTable99

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'myTable99' AND COLUMN_NAME = 'Col2')
ALTER TABLE myTable99 DROP COLUMN Col2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

IF EXISTS

In Books Online it says "The select list of a subquery introduced by EXISTS
almost always consists of an asterisk (*). There is no reason to list column
names because you are simply testing for the existence of rows that meet the
conditions specified in the subquery."
If I've got two tables in my query should I still do SELECT *?
E.g...
IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE
inserted.CustomerSerialNo = deleted.CustomerSerialNo AND inserted.Location
<> deleted.Location)
IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo =
deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
Does it make a difference?
Thanks,
Chris>If I've got two tables in my query should I still do SELECT *?
It doesnt matters.
Personally I prefer SELECT 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris" <cw@.community.nospam> wrote in message
news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> In Books Online it says "The select list of a subquery introduced by
> EXISTS almost always consists of an asterisk (*). There is no reason to
> list column names because you are simply testing for the existence of rows
> that meet the conditions specified in the subquery."
> If I've got two tables in my query should I still do SELECT *?
> E.g...
> IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE
> inserted.CustomerSerialNo = deleted.CustomerSerialNo AND inserted.Location
> <> deleted.Location)
> IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo
> = deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
> Does it make a difference?
> Thanks,
> Chris
>|||No difference. It's the presence or absence of a row that matters, not
what columns there are in that row.
I know some people say that the idea of specifying * is that it lets
the optimizer choose the best plan based on ANY column(s) in the
table/indexes. However, I don't recall ever seeing an example in SQL
Server where the SELECT list in an EXISTS statement makes any
performance difference. AFAIK the optimizer always ignores the columns
you specify. Whatever the case, in logical terms it makes no difference
to the result of the query.
David Portas
SQL Server MVP
--|||Thanks,
Chris
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ONYyWxyRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> It doesnt matters.
> Personally I prefer SELECT 1
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Chris" <cw@.community.nospam> wrote in message
> news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl...
>|||Chris,
I've heard a rumour that select * is a tiny bit faster to execute.
I don't like your old style join syntax though, this may be easier to read:
if exists
( select *
from inserted i
inner join deleted d
on d.CustomerSerialNo = i.CustomerSerialNo
and d.Location <> i.Location
)
Regards
AJ
"Chris" <cw@.community.nospam> wrote in message news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl..
.
> In Books Online it says "The select list of a subquery introduced by EXIST
S almost always consists of an asterisk (*).
> There is no reason to list column names because you are simply testing for
the existence of rows that meet the
> conditions specified in the subquery."
> If I've got two tables in my query should I still do SELECT *?
> E.g...
> IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE i
nserted.CustomerSerialNo =
> deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
> IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo
= deleted.CustomerSerialNo AND
> inserted.Location <> deleted.Location)
> Does it make a difference?
> Thanks,
> Chris
>|||Agree. And, I'm pretty certain that we've been told from MS that this is ind
eed the fact (i.e., SQL
Server isn't stupid enough to actually materialize any column data is simila
r - column list in
EXISTS is irrelevant).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114169328.986401.306840@.z14g2000cwz.googlegroups.com...
> No difference. It's the presence or absence of a row that matters, not
> what columns there are in that row.
> I know some people say that the idea of specifying * is that it lets
> the optimizer choose the best plan based on ANY column(s) in the
> table/indexes. However, I don't recall ever seeing an example in SQL
> Server where the SELECT list in an EXISTS statement makes any
> performance difference. AFAIK the optimizer always ignores the columns
> you specify. Whatever the case, in logical terms it makes no difference
> to the result of the query.
> --
> David Portas
> SQL Server MVP
> --
>|||Great, thanks for the information.
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23frGCO0RFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Agree. And, I'm pretty certain that we've been told from MS that this is
> indeed the fact (i.e., SQL Server isn't stupid enough to actually
> materialize any column data is similar - column list in EXISTS is
> irrelevant).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1114169328.986401.306840@.z14g2000cwz.googlegroups.com...
>|||As David mentioned, we have never seen an example posted that
demonstrated a difference in performance. So I would forget about the
rumour...
Gert-Jan
Andrew John wrote:
> Chris,
> I've heard a rumour that select * is a tiny bit faster to execute.
> I don't like your old style join syntax though, this may be easier to read
:
> if exists
> ( select *
> from inserted i
> inner join deleted d
> on d.CustomerSerialNo = i.CustomerSerialNo
> and d.Location <> i.Location
> )
> Regards
> AJ
> "Chris" <cw@.community.nospam> wrote in message news:OkyubuyRFHA.3156@.TK2MS
FTNGP15.phx.gbl...|||Gert-Jan,
I don't know if I'd call it a performance difference, but if there
are identifiers or literals in the select list of EXISTS, the query
processor must check that they are valid, a step not required
with *. So in at least one sense, SELECT * may be less work
than SELECT 1 or SELECT <column>.
The two queries below produce errors, and I doubt the code that
leads to these errors is executed at all with EXISTS (SELECT * ...).
But I'm sure this code *is* executed with SELECT 1 or SELECT i.
With everything else the query processor has to do, though,
I doubt this difference is measurable, and I believe the
query plan is always the same. Once a plan is cached, also,
there may not be a difference.
create table T (
i int
)
go
select i
from T
where exists (
select X from T
)
go
select i
from T
where exists (
select 9999999999999999999999999999999999999999
from T
)
go
Steve Kass
Drew University
I assume there
Gert-Jan Strik wrote:
>As David mentioned, we have never seen an example posted that
>demonstrated a difference in performance. So I would forget about the
>rumour...
>Gert-Jan
>
>Andrew John wrote:
>|||Right. No example that demonstrates a (measureable) performance
difference!
The code you posted just demonstrates that the query parser does not
allow illegal or inproperly formed queries.
Gert-Jan
Steve Kass wrote:
> Gert-Jan,
> I don't know if I'd call it a performance difference, but if there
> are identifiers or literals in the select list of EXISTS, the query
> processor must check that they are valid, a step not required
> with *. So in at least one sense, SELECT * may be less work
> than SELECT 1 or SELECT <column>.
> The two queries below produce errors, and I doubt the code that
> leads to these errors is executed at all with EXISTS (SELECT * ...).
> But I'm sure this code *is* executed with SELECT 1 or SELECT i.
> With everything else the query processor has to do, though,
> I doubt this difference is measurable, and I believe the
> query plan is always the same. Once a plan is cached, also,
> there may not be a difference.
> create table T (
> i int
> )
> go
> select i
> from T
> where exists (
> select X from T
> )
> go
> select i
> from T
> where exists (
> select 9999999999999999999999999999999999999999
> from T
> )
> go
> Steve Kass
> Drew University
> I assume there
> Gert-Jan Strik wrote:
>

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:

if exists

i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?Does column b exist int able youy are trying to update?
--
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>

if exists

i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?
Does column b exist int able youy are trying to update?
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>
sql

if exists

i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?what version of sql server is the problem in?
are there perhaps other owners of identically named tables?
for example, is there both a mauro.table and a dbo.table, where the
mauro.table has the b column and the dbo.table does not?
Mauro wrote:
> i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>|||2005., NO, the column does not exists in the whole server, in the 2000
server it works perfectly and in 2005 in another databases it also works
fine.
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:Of%23ruGI7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> what version of sql server is the problem in?
> are there perhaps other owners of identically named tables?
> for example, is there both a mauro.table and a dbo.table, where the
> mauro.table has the b column and the dbo.table does not?
> Mauro wrote:
not

if exists

i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?Does column b exist int able youy are trying to update?
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>