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.Level Done,A.LevelPla ned,A.PlanedDat e,A.MatrixConte ntID,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.MatrixConten tID=B.ID AND A.UserID=@useri d AND A.Status<>3)
where B.ParentID=@par entid
ORDER BY B.ElementID,B.E lementPos
END
set @onelevel=''
OPEN level_cursor
FETCH NEXT FROM level_cursor
INTO @pos,@levelid,@ leveldone, @levelplaned,
@planeddate,@el ementid, @levelstatus,@l evelupd,@levela uthor
WHILE @@FETCH_STATUS = 0
BEGIN
set @prevelementid= @elementid
if (@pos IS NULL)
set @onelevel=''
else
set @onelevel=@pos
if (@elementid IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@elementid
if (@levelid IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelid
if (@leveldone IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@leveldone
if (@levelplaned IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelplaned
if (@planeddate IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@planeddate
if (@levelstatus IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelstatus
if (@levelupd IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelupd
if (@levelauthor IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelauthor
-- Part Output
print @onelevel
if (@return is NULL)
exec(@return+@o nelevel)
else
exec(@return+'* ;*'+@onelevel)
FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@el ementid,
@levelstatus,@l evelupd,@levela uthor
if (@prevelementid IS NOT NULL AND @prevelementid= @elementid)
FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@el ementid,
@levelstatus,@l evelupd,@levela uthor
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 '*'.
-----
6  6810 
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.Leve lDone,A.LevelPl aned,A.PlanedDa te,A.MatrixCont entID,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.MatrixConte ntID=B.ID AND A.UserID=@useri d AND A.Status<>3)
where B.ParentID=@par entid
ORDER BY B.ElementID,B.E lementPos
END
set @onelevel=''
OPEN level_cursor
FETCH NEXT FROM level_cursor
INTO @pos,@levelid,@ leveldone, @levelplaned,
@planeddate,@el ementid, @levelstatus,@l evelupd,@levela uthor
WHILE @@FETCH_STATUS = 0
BEGIN
set @prevelementid= @elementid
if (@pos IS NULL)
set @onelevel=''
else
set @onelevel=@pos
if (@elementid IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@elementid
if (@levelid IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelid
if (@leveldone IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@leveldone
if (@levelplaned IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelplaned
if (@planeddate IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@planeddate
if (@levelstatus IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelstatus
if (@levelupd IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelupd
if (@levelauthor IS NULL)
set @onelevel=@onel evel+'*-*'
else
set @onelevel=@onel evel+'*-*'+@levelauthor
-- Part Output
print @onelevel
if (@return is NULL)
exec(@return+@o nelevel)
else
exec(@return+'* ;*'+@onelevel)
FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@e lementid,
@levelstatus,@l evelupd,@levela uthor
if (@prevelementid IS NOT NULL AND @prevelementid= @elementid)
FETCH NEXT FROM level_cursor
INTO @pos,@levelid, @leveldone, @levelplaned,
@planeddate,@e lementid,
@levelstatus,@l evelupd,@levela uthor
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 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+@o nelevel)
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+@o nelevel) I try to fill my @return variable with
the content of @onelevel. I can't do it with set @return=@onelev el
because @return is of type ntext.
How can I do it else?
thanks and bye,
Agnès.
Not sure if you can do this with ntext, but try this
select @return = @return + '*;*' + @onelevel
(re*@infoman.de ) writes:
thanks for your answer.
With exec(@return+@o nelevel) I try to fill my @return variable with
the content of @onelevel. I can't do it with set @return=@onelev el
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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
On 25 Oct 2005 00:35:55 -0700, re*@infoman.de wrote: Hello Helmut,
thanks for your answer.
With exec(@return+@o nelevel) I try to fill my @return variable with
the content of @onelevel. I can't do it with set @return=@onelev el
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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Tod Thames |
last post by:
I currently have a sql statement that works great. I want to convert it
to a stored procedure so I can generate results from a webpage. Below
is the stored procedure that is working fine.
select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
SIDPERS_PERS_UNIT_TBL.UNAME,
SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
. + ' ' + . AS HOR,
SMOSC=(case .
when "1" then "Yes"
|
by: MD |
last post by:
I am trying to create a dynamic SQL statement to create a view.
I have a stored procedure, which based on the parameters passed calls
different stored procedures. Each of this sub stored procedure creates
a string of custom SQL statement and returns this string back to the
main stored procedure.
This SQL statements work fine on there own. The SQL returned from the
sub stored procedure are returned fine. The datatype of the variable
that...
|
by: serge |
last post by:
I can not create a stored procedure that calls another not yet created
stored procedure?
In MS SQL I get a warning that the calling procedure does not exist but the
new stored
Procedure gets created anyhow. I believe Oracle works similarly.
I can not make this possible in DB2?
|
by: dog |
last post by:
I've seen plenty of articles on this topic but none of them have been
able to solve my problem.
I am working with an Access 97 database on an NT4.0 machine, which has
many Access reports.
I want my users to be able to select a report, click on a command
button on a form, which will then automatically create the report as a
pdf file and save it to the user's machine.
I am using Adobe Acrobat (5.0 I think) and have Adobe Distiller as a
|
by: David Shorthouse |
last post by:
Hey folks,
I am attempting to pass null as the input value from a series of
textboxes if the user does not input a value prior to submit. To try and do
this, I am using a vbscript function on this asp as follows:
CommentsAdd = IIf(Request.Form("Comments")="",NULL,Request.Form("Comments"))
I was hoping this would convert those emptystring textboxes to null.
| | |
by: Ornette |
last post by:
Hello,
I have a stored procedure which generates some values in the table.
When I use update() how to populate the dataset with theses values ?
For the moment I use output parameter but it just works for 1 row and as the
dataset doesn't have the value I should put it after and the rowstate goes
to "modified"...
Any ideas ?
|
by: verb13 |
last post by:
I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"
The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a...
|
by: ramuygl |
last post by:
want to create store procedure that. want to send the table name as argument and retrive the data of that argument. and want to store data in temperary table using the insert query.
HERE I AM ATTACHING A SAMPLE PROCEDURE IT WAS NOT WORKING PERFECTLY. HELP ME
--exec SP_Generate_Insert_Statements tbl_Attachment,1
--select * from tbl_Attachmenttesting
--exec SP_Generate_Insert_Statements tbl_Attachmenttesting,1
ALTER PROCEDURE .
|
by: Snow |
last post by:
Hello:
I want to find that the ntext column data string have more than 2000
characters. I need to truncate those strings to the segments with 200
character, then put those segments along with their table_name and
column_name to another table. Maybe need to use cursor? If so, how to
use it?
Your help is highly appreciated.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
