Posted by Steve Jorgensen on 10/24/05 18:36

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.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]