Create Procedure Output ntext

Hello,

I need to produce with T-SQL a user defined function or stored
procedure that make one SLQ-Statement and prepare as string from the
result set.
The request muss be able to return a very long unicode string. The
return value nvarchar is being truncated so I'm trying to create a
stored procedure, that returns a ntext string.

I can't manage it (I am no T-SQL specialist). Maybe someone can help
me?

Thanks for your help.

-----
Here is my sp:
alter procedure F_FUNCTION (@userid int, @parentid int, @status int,
@return ntext output)
AS
BEGIN
DECLARE @onelevel nvarchar(4000)
DECLARE @pos varchar(1000)
DECLARE @leveldone varchar(100)
DECLARE @levelplaned varchar(100)
DECLARE @planeddate nvarchar(4000)
DECLARE @elementid varchar(10)
DECLARE @levelid varchar(10)
DECLARE @levelstatus varchar(10)
DECLARE @levelupd nvarchar(4000)
DECLARE @levelauthor varchar(10)
DECLARE @prevelementid varchar(10)

BEGIN
declare level_cursor CURSOR FOR
SELECT
B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A.PlanedDate,A.MatrixConten
tID,A.Status,
convert(varchar,A.Upd,126) as Upd,A.Author
FROM T_TABLE1 as B left outer join T_TABLE2 as A on
(A.MatrixContentID=B.ID AND A.UserID=@userid AND A.Status<>3)
where B.ParentID=@parentid
ORDER BY B.ElementID,B.ElementPos
END

set @onelevel=''

OPEN level_cursor

FETCH NEXT FROM level_cursor
INTO  @pos,@levelid,@level
done, @levelplaned,
 @planeddate,@element
id,  @levelstatus,@levelu
pd,@levelauthor

WHILE @@FETCH_STATUS = 0
BEGIN
set  @prevelementid=@elem
entid

if (@pos IS NULL)
set @onelevel=''
else
set @onelevel=@pos

if (@elementid IS NULL)
set  @onelevel=@onelevel+
'*-*'
else
set  @onelevel=@onelevel+
'*-*'+@elementid

if (@levelid IS NULL)
set  @onelevel=@onelevel+
'*-*'
else
set  @onelevel=@onelevel+
'*-*'+@levelid

if (@leveldone IS NULL)
set  @onelevel=@onelevel+
'*-*'
else
set  @onelevel=@onelevel+
'*-*'+@leveldone

if (@levelplaned IS NULL)
set  @onelevel=@onelevel+
'*-*'
else
set  @onelevel=@onelevel+
'*-*'+@levelplaned

if (@planeddate IS NULL)
set  @onelevel=@onelevel+
'*-*'
else
set  @onelevel=@onelevel+
'*-*'+@planeddate

if (@levelstatus IS NULL)
set  @onelevel=@onelevel+
'*-*'
else
set  @onelevel=@onelevel+
'*-*'+@levelstatus

if (@levelupd IS NULL)
set  @onelevel=@onelevel+
'*-*'
else
set  @onelevel=@onelevel+
'*-*'+@levelupd

if (@levelauthor IS NULL)
set  @onelevel=@onelevel+
'*-*'
else
set  @onelevel=@onelevel+
'*-*'+@levelauthor

-- Part Output
print @onelevel

if (@return is NULL)
 exec(@return+@onelev
el)
else
 exec(@return+'*;*'+@
onelevel)

FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
 @planeddate,@element
id,
 @levelstatus,@levelu
pd,@levelauthor

if (@prevelementid IS NOT NULL AND  @prevelementid=@elem
entid)
FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
 @planeddate,@element
id,
 @levelstatus,@levelu
pd,@levelauthor

END

CLOSE level_cursor
DEALLOCATE level_cursor

RETURN

END

-----
Call of the function with:
exec dbo.F_FUNCTION 550,1632, 0, ''

Here the beginning of the query analyser output:

1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.

-----

I think you cannot return that using an output parameter - you'll have to
return it as a field in a SELECT.


On 24 Oct 2005 08:17:01 -0700, rey@infoman.de wrote:

>Hello,
>
>I need to produce with T-SQL a user defined function or stored
>procedure that make one SLQ-Statement and prepare as string from the
>result set.
>The request muss be able to return a very long unicode string. The
>return value nvarchar is being truncated so I'm trying to create a
>stored procedure, that returns a ntext string.
>
>I can't manage it (I am no T-SQL specialist). Maybe someone can help
>me?
>
>Thanks for your help.
>
>-----
>Here is my sp:
>alter procedure F_FUNCTION (@userid int, @parentid int, @status int,
>@return ntext output)
>AS
>BEGIN
>   DECLARE @onelevel nvarchar(4000)
>   DECLARE @pos varchar(1000)
>   DECLARE @leveldone varchar(100)
>   DECLARE @levelplaned varchar(100)
>   DECLARE @planeddate nvarchar(4000)
>   DECLARE @elementid varchar(10)
>   DECLARE @levelid varchar(10)
>   DECLARE @levelstatus varchar(10)
>   DECLARE @levelupd nvarchar(4000)
>   DECLARE @levelauthor varchar(10)
>   DECLARE @prevelementid varchar(10)
>
>   BEGIN
>   declare level_cursor CURSOR FOR
>   SELECT
>B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A.PlanedDate,A.MatrixConte
ntID,A.Status,
>	convert(varchar,A.Upd,126) as Upd,A.Author
>   FROM T_TABLE1 as B left outer join T_TABLE2 as A on
>(A.MatrixContentID=B.ID AND A.UserID=@userid AND A.Status<>3)
>   where B.ParentID=@parentid
>   ORDER BY B.ElementID,B.ElementPos
>   END
>
>   set @onelevel=''
>
>   OPEN level_cursor
>
>   FETCH NEXT FROM level_cursor
>   INTO  @pos,@levelid,@level
done, @levelplaned,
>    @planeddate,@element
id,  @levelstatus,@levelu
pd,@levelauthor
>
>   WHILE @@FETCH_STATUS = 0
>   BEGIN
>	set  @prevelementid=@elem
entid
>
>	 if (@pos IS NULL)
>	   set @onelevel=''
>         else
>	   set @onelevel=@pos
>
>	 if (@elementid IS NULL)
>	   set  @onelevel=@onelevel+
'*-*'
>         else
>	   set  @onelevel=@onelevel+
'*-*'+@elementid
>
>	 if (@levelid IS NULL)
>	   set  @onelevel=@onelevel+
'*-*'
>         else
>	   set  @onelevel=@onelevel+
'*-*'+@levelid
>
>	 if (@leveldone IS NULL)
>	   set  @onelevel=@onelevel+
'*-*'
>         else
>	   set  @onelevel=@onelevel+
'*-*'+@leveldone
>
>	 if (@levelplaned IS NULL)
>	   set  @onelevel=@onelevel+
'*-*'
>         else
>	   set  @onelevel=@onelevel+
'*-*'+@levelplaned
>
>	 if (@planeddate IS NULL)
>	   set  @onelevel=@onelevel+
'*-*'
>         else
>	   set  @onelevel=@onelevel+
'*-*'+@planeddate
>
>	 if (@levelstatus IS NULL)
>	   set  @onelevel=@onelevel+
'*-*'
>         else
>	   set  @onelevel=@onelevel+
'*-*'+@levelstatus
>
>	 if (@levelupd IS NULL)
>	   set  @onelevel=@onelevel+
'*-*'
>         else
>	   set  @onelevel=@onelevel+
'*-*'+@levelupd
>
>	 if (@levelauthor IS NULL)
>	   set  @onelevel=@onelevel+
'*-*'
>         else
>	   set  @onelevel=@onelevel+
'*-*'+@levelauthor
>
>       -- Part Output
>       print @onelevel
>
>       if (@return is NULL)
>	  exec(@return+@onelev
el)
>       else
>          exec(@return+'*;*'+@
onelevel)
>
>   FETCH NEXT FROM level_cursor
>   INTO @pos,@levelid, @leveldone, @levelplaned,
> @planeddate,@element
id,
>    @levelstatus,@levelu
pd,@levelauthor
>
>   if (@prevelementid IS NOT NULL AND  @prevelementid=@elem
entid)
>   FETCH NEXT FROM level_cursor
>   INTO @pos,@levelid, @leveldone, @levelplaned,
> @planeddate,@element
id,
>    @levelstatus,@levelu
pd,@levelauthor
>
>   END
>
>   CLOSE level_cursor
>   DEALLOCATE level_cursor
>
>  RETURN
>
>END
>
>-----
>Call of the function with:
>exec dbo.F_FUNCTION 550,1632, 0, ''
>
>Here the beginning of the query analyser output:
>
>1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '*'.
>
>-----

Am 24 Oct 2005 08:17:01 -0700 schrieb rey@infoman.de:

...
> -----
> Call of the function with:
> exec dbo.F_FUNCTION 550,1632, 0, ''
>
> Here the beginning of the query analyser output:
>
> 1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '*'.
>
> -----

ntext is a valid datatype for the OUTPUT variable of a stored procedure.
This is not the problem. Your error appears here:
...
-- Part Output
print @onelevel

if (@return is NULL)
 exec(@return+@onelev
el)
else
 exec(@return+'*;*'+@
onelevel)
...
because if @return is Null then you do a
exec('1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277')
and what should this be?
EXEC starts another stored proc, from there you get your error message. And
so it is an error in line 1.

bye
Helmut

Hello Helmut,

thanks for your answer.
With  exec(@return+@onelev
el)  I try to fill my @return variable with
the content of @onelevel. I can't do it with set @return=3D@onelevel
because @return is of type ntext.

How can I do it else?

thanks and bye,
Agn=E8s.

Not sure if you can do this with ntext, but try this

select @return = @return + '*;*' + @onelevel

(rey@infoman.de)  writes:
>
> thanks for your answer.
> With  exec(@return+@onelev
el)  I try to fill my @return variable with
> the content of @onelevel. I can't do it with set @return=@onelevel
> because @return is of type ntext.
>
> How can I do it else?

You can't. Since you are in a dead end, I suggest that you explain
your underlying business problem that you are trying to solve. What
does the calling side of this look like?

I can offer one workaround: move to SQL 2005, which offers the
new datatype nvarchar(MAX), which in difference to ntext is a first
class citizen.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

On 25 Oct 2005 00:35:55 -0700, rey@infoman.de wrote:

>Hello Helmut,
>
>thanks for your answer.
>With  exec(@return+@onelev
el)  I try to fill my @return variable with
>the content of @onelevel. I can't do it with set @return=@onelevel
>because @return is of type ntext.
>
>How can I do it else?
>
>thanks and bye,
>Agnès.

What's wrong with returning it via SELECT rather than via a parameter?