469,111 Members | 1,961 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

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

-----

Oct 24 '05 #1
6 6594
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 '*'.

-----


Oct 24 '05 #2
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
Oct 24 '05 #3
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.

Oct 25 '05 #4
Not sure if you can do this with ntext, but try this

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

Oct 25 '05 #5
(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

Oct 25 '05 #6
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?
Oct 26 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Tod Thames | last post: by
8 posts views Thread by Ornette | last post: by
4 posts views Thread by Snow | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.