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 '*'.
--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 '*'.
>--|||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+@.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|||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,
Agns.|||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+@.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, 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+@.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,
>Agns.
What's wrong with returning it via SELECT rather than via a parameter?
No comments:
Post a Comment