Determine if field that is declared as ntext, text is null

[Guest_imported]

I get error 403: Invalid operator for data typs. Operator equals add, type equals text.


this is my code

Question1 and Question2 are ntext.

Select case
when Question1 is not null then
'adfasada'
when Question2 is not null then
'ssdgsd'
end as HTML
form

where
Chapter = @chapter

 

[tlbroadbent]

Use the datalength function.

When datalength(Question1)>0 Then 'adfasada' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

I tried using the datalength function as you suggested, but i still get the same error 403

thands

 

[tlbroadbent]

Which version of SQL server are you running? There are differences between versions. Both the test for IS NOT NULL and datalength()>0 work in SQL 2000.

Here is another recommendation.

When substring(Question1,1,1) Is Not Null Then 'adfasada' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

Im using SQL Server 2000.

Your right about the test for is not null and datalength, both work. However if the datatypes are text, ntext they dont seem to work.

 

[tlbroadbent]

I tested with both text and ntext data types.

IS NULL works
Datalength actually return NULL if the column is NULL
Substring works

How are you executing the query? I've executed from an Access 2000 project and query analyzer. Again, both worked.

What is the compatibility level of the database? If it is less than 70, that may cause a problem. Execute sp_dbcmptlevel 'db_name' to check. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

Im using this query to create a stored procedure, every time i check the syntax I get error 403

My compatibility level is set to 70 should it be higher than that??


I also tried this query inside sql servers query analyzer, and i still get error 403.

thanks

 

[tlbroadbent]

Are you running the base version of SQL 2000 or SP1? I'm running SP1, that might be the difference.

Could you post the entire SP here so I could analyze it? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

CREATE PROCEDURE [dbo].[sp_LeadTheFieldwithHTML]
(@Chapter as int)



AS



---VERIFY INPUT
if @Chapter = '' goto abort


---GET THE FEATURED AUTHORS
SELECT

CASE --write the image

when datalength(Question1) <> 0 then
'<h2><b><font face=&quot;Arial, Helvetica, sansserif&quot;>' + ChapterTitle + '</font></b></h2><table width=&quot;75%&quot; border=&quot;0&quot;><tr> <td colspan=&quot;2&quot;>' + Question1 + '</td></tr>'

when Response1a <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer1&quot; value=&quot;Answer1a&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response1a + '</td></tr>'
when Response1b <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer1&quot; value=&quot;Answer1b&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response1b + '</td></tr>'

when Response1c <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer1&quot; value=&quot;Answer1b&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response1c + '</td></tr>'

when Response1d <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer1&quot; value=&quot;Answer1d&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response1d + '</td></tr>'

when Response1e <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer1&quot; value=&quot;Answer1e&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response1e + '</td></tr>'

when Question2 is not null then
'<tr><td colspan=&quot;2&quot;>&nbsp;</td></tr><tr><td colspan=&quot;2&quot;>' + Question2 +'</td>'+'</tr>' */

when Response2a <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer2&quot; value=&quot;Answer2a&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response2a + '</td></tr>'

when Response2b <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer2&quot; value=&quot;Answer2b&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response2b + '</td></tr>'

when Response2c <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer2&quot; value=&quot;Answer2c&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response2c + '</td></tr>'

when Response2d <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer2&quot; value=&quot;Answer2d&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response2d + '</td></tr>'

when Response2e <> '' then
'<tr><td width=&quot;5%&quot;><input type=&quot;radio&quot; name=&quot;Answer2&quot; value=&quot;Answer2e&quot;></td>'+
'<td width=&quot;95%&quot;>' + Response2e + '</td></tr>'

else ''

END as

	FROM
	System_LeadTheFieldResponses
			 

	WHERE
	Chapter = @Chapter

	
ABORT:
GO

 

[tlbroadbent]

We've been chasing the wrong problem. The cause of the failure is concatenating ntext and text columns. It can't be done using the + operator.

If possible, change the column data type to nvarchar or varchar. Otherwise, your stored procedure will bcome more complex as you have to deal with text pointers, cursors and looping. I've been able to avoid using TEXT/NTEXT columns so I don't have much experience dealing with them. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

Thanks for your help

Noel