Problem with ntext in a stored procedure

[Vick]

Hi,

my table : t_test ( id_user int, infos ntext )

my proc :
create procedure pr_mytest @linfo ntext, @iduser int, @resulat int output
as
declare @sql varchar(8000)
BEGIN TRAN
SELECT @sql = 'update t_test set infos=''' + @linfo + ''' WHERE id_user
= ' + CAST(@iduser as varchar(16)) + '
EXEC(@sql)
select @resulat = 0
COMMIT
GO

When I compile that procedure I get an error message :
Serveur : Msg 403, Niveau 16, État 1, Procédure pr_mytest, Ligne 6
Invalid operator for data type. Operator equals add, type equals ntext.


Could you help me to resolve that problem.


Thanks a lot.

[David Satz]

you cannot use the image, ntext, or text data types in a + (String
Concatenation) operation

why are you using dynamic SQL anyway why not just:
update t_test
set infos= @linfo
WHERE id_user= @iduser


--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Vick" <v_rama@club-internet.fr> wrote in message
news:3cff548a$1@10.1.10.29...
>
> Hi,
>
> my table : t_test ( id_user int, infos ntext )
>
> my proc :
> create procedure pr_mytest @linfo ntext, @iduser int, @resulat int output
> as
> declare @sql varchar(8000)
> BEGIN TRAN
> SELECT @sql = 'update t_test set infos=''' + @linfo + ''' WHERE
id_user
> = ' + CAST(@iduser as varchar(16)) + '
> EXEC(@sql)
> select @resulat = 0
> COMMIT
> GO
>
> When I compile that procedure I get an error message :
> Serveur : Msg 403, Niveau 16, État 1, Procédure pr_mytest, Ligne 6
> Invalid operator for data type. Operator equals add, type equals ntext.
>
>
> Could you help me to resolve that problem.
>
>
> Thanks a lot.
>

[Vick]

Hi,

Ok, I use an dynamic SQL because I need to build the query because the table
name change. I have 15 tables t_test, for example : t_test_X (X=1..15)
and in function the iduser I execute my query in the table t_test_X
Sorry, my example is not complete.
So, how can I do to use a dynamic SQL in my case, or if you have an other
solution ...

Thanks you


"David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>you cannot use the image, ntext, or text data types in a + (String
>Concatenation) operation
>
>why are you using dynamic SQL anyway why not just:
>update t_test
>set infos= @linfo
>WHERE id_user= @iduser
>
>
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"Vick" <v_rama@club-internet.fr> wrote in message
>news:3cff548a$1@10.1.10.29...
>>
>> Hi,
>>
>> my table : t_test ( id_user int, infos ntext )
>>
>> my proc :
>> create procedure pr_mytest @linfo ntext, @iduser int, @resulat int output
>> as
>> declare @sql varchar(8000)
>> BEGIN TRAN
>> SELECT @sql = 'update t_test set infos=''' + @linfo + ''' WHERE
>id_user
>> = ' + CAST(@iduser as varchar(16)) + '
>> EXEC(@sql)
>> select @resulat = 0
>> COMMIT
>> GO
>>
>> When I compile that procedure I get an error message :
>> Serveur : Msg 403, Niveau 16, État 1, Procédure pr_mytest, Ligne 6
>> Invalid operator for data type. Operator equals add, type equals ntext.
>>
>>
>> Could you help me to resolve that problem.
>>
>>
>> Thanks a lot.
>>
>
>

[David Satz]

you should probably not use dynamic SQL (read
http://www.algonet.se/~sommar/dynamic_sql.html) ; but if you must you need
to change the datatype of @linfo from ntext nvarchar or use cast( @linfo as
nvarchar(1000))

Dave
"Vick" <v_rama@club-internet.fr> wrote in message
news:3cff77e6$1@10.1.10.29...
>
> Hi,
>
> Ok, I use an dynamic SQL because I need to build the query because the
table
> name change. I have 15 tables t_test, for example : t_test_X (X=1..15)
> and in function the iduser I execute my query in the table t_test_X
> Sorry, my example is not complete.
> So, how can I do to use a dynamic SQL in my case, or if you have an other
> solution ...
>
> Thanks you
>
>
> "David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
> >you cannot use the image, ntext, or text data types in a + (String
> >Concatenation) operation
> >
> >why are you using dynamic SQL anyway why not just:
> >update t_test
> >set infos= @linfo
> >WHERE id_user= @iduser
> >
> >
> >--
> >HTH,
> >David Satz
> >Principal Web Engineer
> >Hyperion Solutions
> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
> >(Please reply to group only - emails answered rarely)
> >-----------------------------------------------------------------
> >"Vick" <v_rama@club-internet.fr> wrote in message
> >news:3cff548a$1@10.1.10.29...
> >>
> >> Hi,
> >>
> >> my table : t_test ( id_user int, infos ntext )
> >>
> >> my proc :
> >> create procedure pr_mytest @linfo ntext, @iduser int, @resulat int
output
> >> as
> >> declare @sql varchar(8000)
> >> BEGIN TRAN
> >> SELECT @sql = 'update t_test set infos=''' + @linfo + ''' WHERE
> >id_user
> >> = ' + CAST(@iduser as varchar(16)) + '
> >> EXEC(@sql)
> >> select @resulat = 0
> >> COMMIT
> >> GO
> >>
> >> When I compile that procedure I get an error message :
> >> Serveur : Msg 403, Niveau 16, État 1, Procédure pr_mytest, Ligne 6
> >> Invalid operator for data type. Operator equals add, type equals ntext.
> >>
> >>
> >> Could you help me to resolve that problem.
> >>
> >>
> >> Thanks a lot.
> >>
> >
> >
>