Text Parameter in Sybase Stored Procedure

[DilipKS]

Hi
I am facing this problem. On compiling this stored procdure it says "TEXT or IMAGE datatypes are invalid for paramters or local variable"
I have OriginalMessage field which containts big XML and its not possible for me to change its datatype from text to some varchar ...any Hope I can still do it ?



CREATE PROCEDURE dbo.orgmsg_upd
@ExcpMsgID int,
@OriginalMessage text
AS
BEGIN
UPDATE SystemExceptions
SET OriginalMessage = @OriginalMessage
WHERE ExcpMsgID = @ExcpMsgID

END

 

[leyka]

You have wrong syntax.
Use round brackets in proc's header

CREATE PROCEDURE dbo.orgmsg_upd
( @ExcpMsgID int,
 @OriginalMessage text)
AS
BEGIN
  UPDATE   SystemExceptions
  SET      OriginalMessage = @OriginalMessage
  WHERE    ExcpMsgID = @ExcpMsgID         
END

 

[mjuell]

The syntax isnt wrong. dont need bracets in header..


Restrictions on text and image Columns

text and image columns cannot be used:
As parameters to stored procedures or as values passed to these parameters
As local variables
In order by, compute, group by, and union clauses
In an index
In subqueries or joins
In a where clause, except with the keyword like
With the + concatenation operator
In the if update clause of a trigger

 

[DilipKS]

Hi Guys..
ya its limitation that we cant use text or image datatype as parameter in Sybase Stored procedure.
So I have dropped the idea of using stored procedure, and using directly "Update" Query SQL .
Thnks for ur replys.
Cheers
Dil

 

[leyka]

// mjuell
// The syntax isnt wrong. dont need bracets in header..

I mean ASA 6.0
I have no problem with text data type in params.
And my example works fine

 

[DilipKS]

whats ASA 6.0 ?
I tried ur example but it was not working . ??

 

[leyka]

Hm...

1. I use:
Adaptive Server Anywere 6.0.4.3763

2. I have a table named "TEST"

create table dba.test (id int default autoincrement not null, mes text default '' null) ;

3. I have a procededure named "DBA.TEST_PROC"

alter procedure DBA.TEST_PROC(@id integer,@mes TEXT)
as
begin
  update dba.test set  mes = @mes
  where
    id = @id
end

4. this line works perfect in ISQL:

exec dba.test_proc @id = 1 , @mes = 'test';