|
|
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 '*'.
-----
Navigation:
[Reply to this message]
|