Microsoft SQL Server forum - Create Procedure Output ntext

Author

Create Procedure Output ntext

rey@infoman.de

2005-10-27, 9:25 am

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,@level
done, @levelplaned,
@planeddate,@element
id, @levelstatus,@levelu
pd,@levelauthor

WHILE @@FETCH_STATUS = 0
BEGIN
set @prevelementid=@elem
entid

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+@onelev
el)
else
exec(@return+'*;*'+@
onelevel)

FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@element
id,
@levelstatus,@levelu
pd,@levelauthor

if (@prevelementid IS NOT NULL AND @prevelementid=@elem
entid)
FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@element
id,
@levelstatus,@levelu
pd,@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 '*'.

-----

Steve Jorgensen

2005-10-27, 9:25 am

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,@level
done, @levelplaned,
> @planeddate,@element
id, @levelstatus,@levelu
pd,@levelauthor
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
> set @prevelementid=@elem
entid
>
> 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+@onelev
el)
> else
> exec(@return+'*;*'+@
onelevel)
>
> FETCH NEXT FROM level_cursor
> INTO @pos,@levelid, @leveldone, @levelplaned,
> @planeddate,@element
id,
> @levelstatus,@levelu
pd,@levelauthor
>
> if (@prevelementid IS NOT NULL AND @prevelementid=@elem
entid)
> FETCH NEXT FROM level_cursor
> INTO @pos,@levelid, @leveldone, @levelplaned,
> @planeddate,@element
id,
> @levelstatus,@levelu
pd,@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 '*'.
>
>-----

helmut woess

2005-10-27, 9:25 am

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+@onelev
el)
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

rey@infoman.de

2005-10-27, 9:25 am

Hello Helmut,

thanks for your answer.
With exec(@return+@onelev
el) I try to fill my @return variable with
the content of @onelevel. I can't do it with set @return=3D@onelevel
because @return is of type ntext.

How can I do it else?

thanks and bye,
Agn=E8s.

pb648174

2005-10-27, 9:25 am

Not sure if you can do this with ntext, but try this

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

Erland Sommarskog

2005-10-27, 9:25 am

(rey@infoman.de) writes:
>
> thanks for your answer.
> With exec(@return+@onelev
el) 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

Steve Jorgensen

2005-10-27, 9:25 am

On 25 Oct 2005 00:35:55 -0700, rey@infoman.de wrote:

>Hello Helmut,
>
>thanks for your answer.
>With exec(@return+@onelev
el) 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?

Sponsored Links