Posted by rey on 10/24/05 18:17

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 '*'.

-----

[Back to original message]