sql server programming: Hello all

I have a problem that seems trivial but I can't seem to solve it. Lets say I
have a table called Notes with 2 fields:

ID - integer
Content - ntext

I would like to write a stored procedure that take an ID as input parameter
and returns the corresponding content as an output parameter. So, the SP
would start with something like this

CREATE PROCEDURE sp_Get_Note_Content (
@ID int,
@content ntext Output
) AS
.....
GO

What would be the body of this stored procedure? How do I retrieve the field
and place it into the @content parameter? The regular "SELECT
@content=Content FROM Notes WHERE ID=@ID" doesn't work.

Any help is GREATLY appreciated.

Regards,

Dmitry

You can't put an ntext into a variable. You're going to have to give up the
idea of the output variable and use a plain SELECT statement ... and have
the app grab the value slightly differently.

--
http://www.aspfaq.com/
(Reverse address to reply.)




[quoted text, click to view]

Dmitry,

As far as I know, ntext parameters can be specified with OUTPUT, but they
cannot be modified within the stored procedure. My best wild guess is that
specifying OUTPUT is available to allow pass-by-reference as a
parameter-passing mechanism for some procedure calling interfaces. Even if
you could write the procedure, you couldn't call it to take advantage of the
OUTPUT parameter, at least from T-SQL, since you can't declare a local
variable of type ntext, which you'd need to pass for the OUTPUT.

If it won't suffice to have the sp return a recordset directly, as in

create procedure p (
@id int
) as
select myNtextColumn
from myTable
where myID = @id

You could try something like this:

create table NtextHolder (
id uniqueidentifier primary key,
txt ntext
)
go

create procedure returnNtext (
@i int,
@u uniqueidentifier output
) as
set @u = newid()
insert into NtextHolder
select @u, Notes
from Northwind..Employees
where EmployeeID = @i
go

declare @u uniqueidentifier
exec returnNtext 5, @u output
select txt from NtextHolder
where id = @u
go

drop procedure returnNtext
drop table NtextHolder

-- Steve Kass
-- Drew University
-- Ref: 2487972B-67F3-41F3-8A4C-1C7D09796D94

[quoted text, click to view]