isnull and ntext variables (short question)

[Matik]

Hello to all,

Below the sample code:

declare @arg_szMsgText ntext
set @arg_szMsgText = isnull(@arg_szMsgText, N'unknown message')

Now the error message I get:

"The assignment operator operation cannot take a ntext data type as an
argument."

Explanation:
I'm using it in a SP. I was trying thousand things to make it running,
and the only way I maked was, I get it when I set the return value from
isnull fucntion to nvarchar or varchar variable.
The problem is, the field in DB, where the value of @arg_szMsgText is
written) is ntext, so I need to use this format.

In the description of isnull function (help) is written:

----------------------------------------------------
Arguments
check_expression

Is the expression to be checked for NULL. check_expression can be of
any type.

replacement_value

Is the expression to be returned if check_expression is NULL.
replacement_value must have the same type as check_expresssion.

Return Types
Returns the same type as check_expression.

-----------------------------------------------------

so ite means, I should be able to use this in such a way ...

I know I can assign the value in other way (like by if @variable is
null ....), but if there is already function doing this, why not to use
it, if it helds the code more clear?

Any ideas? Maybe I'm doing something wrong.

Matik

[Simon Hayes]

"Matik" <ma****@sauron.xo.pl> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hello to all,

Below the sample code:

declare @arg_szMsgText ntext
set @arg_szMsgText = isnull(@arg_szMsgText, N'unknown message')

Now the error message I get:

"The assignment operator operation cannot take a ntext data type as an
argument."

<snip>

I'm not sure where you're running those commands - I got the following (much
clearer) message using Query Analyzer:

Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

See DECLARE in Books Online - you can't declare an ntext variable, although
you can use ntext for stored procedure parameters. If you can give more
details of what you're trying to do, someone may have a suggestion on what
else you can do.

Simon

[Matik]

Hej Simon,

Thank you for your reply.

I'm using it in a stored procedure, as an input parameter, but then, I
need to insert it as a value of collumn into table.
Because the value passed by calling stored procedure can be null, I
need to avoid inserting null's into my table. That's why, I thought I
can jus use this simple function, to avoid carrying empty (null) data
value in this variable.

Any other ideas?

Matik

PS I get this message, when I try to alter my stored procedure...

[Erland Sommarskog]

Matik (ma****@sauron.xo.pl) writes:

Thank you for your reply.

I'm using it in a stored procedure, as an input parameter, but then, I
need to insert it as a value of collumn into table.
Because the value passed by calling stored procedure can be null, I
need to avoid inserting null's into my table. That's why, I thought I
can jus use this simple function, to avoid carrying empty (null) data
value in this variable.

Any other ideas?

INSERT tbl (.... ntextcol)
VALUES (.... isnull(@ntextpar, N'Unknown message')

or

INSERT tbl (.... ntextcol)
VALUES (.... coalesce(@ntextpar, N'Unknown message')

of if none of that works out:

if @nextpar IS NOT NULL
INSERT tbl (.... ntextcol)
VALUES (.... @ntextpar)
else
INSERT tbl (.... ntextcol)
VALUES (.... N'Unknown message')
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp