SQL: IF EXISTS... using TEXT field

[Darrel]

I'm trying to use a IF EXISTS (SELECT...) UPDATE... ELSE INSERT... sql
query.

This works until I try to use it with a text field type. When I do that, I
get an error saying ntext can't be used in this type of aggregate query.

Ugh.

So, is there solution to this via SQL? Or do I manually need to write three
separate queries and connections: the first to see if it exists, thena
separate UPDATE or INSERT function?

-Darrel

[dmm]

Re: IF EXISTS... using TEXT field

not sure this is the correct group, but you should use 'if select 1 from xxx
where xxxx' as in the code below:


--won't work
if exists
(
select
cast ('fred' as ntext)
where 1 = 1
)
begin
print 'exists'
end
else
print 'doesnt exist'

--so use select 1 with the same where conditions
if exists
(
select
1
where 1 = 1

)
begin
print 'exists'
end
else
print 'doesnt exist'


HTHs



"Darrel" <notreal@nospam .com> wrote in message
news:%23s13okbg GHA.764@TK2MSFT NGP03.phx.gbl.. .[color=blue]
> I'm trying to use a IF EXISTS (SELECT...) UPDATE... ELSE INSERT... sql
> query.
>
> This works until I try to use it with a text field type. When I do that, I
> get an error saying ntext can't be used in this type of aggregate query.
>
> Ugh.
>
> So, is there solution to this via SQL? Or do I manually need to write
> three separate queries and connections: the first to see if it exists,
> thena separate UPDATE or INSERT function?
>
> -Darrel
>[/color]

[Darrel]

Re: IF EXISTS... using TEXT field

> not sure this is the correct group, but you should use 'if select 1 from[color=blue]
> xxx where xxxx' as in the code below:[/color]

I'm not quite following that. I'm not using a SP, btw. Here's my actual
query I'd like to use:

IF EXISTS (SELECT varType, longVarValue FROM table WHERE varType =
'siteMenu') UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
ELSE INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)

With longVarValue being of type 'text' in the DB. Do I need to case that as
'ntext'?

-Darrel

[dmm]

Re: IF EXISTS... using TEXT field

Sorry, poor communication skillz ;)

I was trying to point out that actually it is the EXISTS keyword that
doesn't like being given NText. Rather than selecting out the actual value,
if you keep your origonal WHERE predicates you may simply select 1 (logical
true). The rest of your query may remain the same.

try the following:

IF EXISTS
(
SELECT
1
FROM
table
WHERE
varType = 'siteMenu'
)
UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
ELSE
INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)



"Darrel" <notreal@nospam .com> wrote in message
news:u8Cin6bgGH A.3860@TK2MSFTN GP02.phx.gbl...[color=blue][color=green]
>> not sure this is the correct group, but you should use 'if select 1 from
>> xxx where xxxx' as in the code below:[/color]
>
> I'm not quite following that. I'm not using a SP, btw. Here's my actual
> query I'd like to use:
>
> IF EXISTS (SELECT varType, longVarValue FROM table WHERE varType =
> 'siteMenu') UPDATE table SET longVarValue = ? WHERE varType = 'siteMenu'
> ELSE INSERT INTO table (varType, longVarValue) VALUES ('siteMenu', ?)
>
> With longVarValue being of type 'text' in the DB. Do I need to case that
> as 'ntext'?
>
> -Darrel
>
>[/color]

[Darrel]

Re: IF EXISTS... using TEXT field

> I was trying to point out that actually it is the EXISTS keyword that[color=blue]
> doesn't like being given NText.[/color]

Argh! USER ERROR! ;o)

I completely missed that. Thanks for pointing it out!

-Darrel