Create Procedure Output ntext

[rey]

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.MatrixContentID,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,@leveldone, @levelplaned,
@planeddate,@elementid, @levelstatus,@levelupd,@levelauthor

WHILE @@FETCH_STATUS = 0
BEGIN
set @prevelementid=@elementid

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+@onelevel)
else
exec(@return+'*;*'+@onelevel)

FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@elementid,
@levelstatus,@levelupd,@levelauthor

if (@prevelementid IS NOT NULL AND @prevelementid=@elementid)
FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@elementid,
@levelstatus,@levelupd,@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 '*'.

-----

[Steve Jorgensen]

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, re*@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.MatrixContentID,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,@leveldone, @levelplaned,
@planeddate,@elementid, @levelstatus,@levelupd,@levelauthor

WHILE @@FETCH_STATUS = 0
BEGIN
set @prevelementid=@elementid

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+@onelevel)
else
exec(@return+'*;*'+@onelevel)

FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@elementid,
@levelstatus,@levelupd,@levelauthor

if (@prevelementid IS NOT NULL AND @prevelementid=@elementid)
FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@elementid,
@levelstatus,@levelupd,@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 '*'.

-----

[helmut woess]

Am 24 Oct 2005 08:17:01 -0700 schrieb re*@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+@onelevel)
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

[rey]

Hello Helmut,

thanks for your answer.
With exec(@return+@onelevel) 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.

[pb648174]

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

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

[Erland Sommarskog]

(re*@infoman.de) writes:

thanks for your answer.
With exec(@return+@onelevel) 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, es****@sommarskog.se

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

[Steve Jorgensen]

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

Hello Helmut,

thanks for your answer.
With exec(@return+@onelevel) 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?