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]

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]

not sure this is the correct group, but you should use 'if select 1 from

xxx where xxxx' as in the code below:

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]

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]

I was trying to point out that actually it is the EXISTS keyword that

doesn't like being given NText.

Argh! USER ERROR! ;o)

I completely missed that. Thanks for pointing it out!

-Darrel