469,276 Members | 1,720 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

SQL Server Stored Procedure with Text Field

Hello,

Does anyone have an example of an SQL Server stored procedure that updates a
record, where one of its field is of type "text"? My procedure is

/*
** Update the client note and production cycle in a Sites record. Set the
** When_submitted field to the current date/time.
*/
CREATE PROCEDURE spSiteNotePCycle
@ID int,
@Client_notes text,
@PCycle char(14)
AS
UPDATE Sites
SET Client_notes=@Client_notes, When_submitted={ fn NOW() }, PCycle=@PCycle
WHERE (ID = @ID)
GO

The procedure executes successfully, and after the update I get the expected
results in the When_submitted and PCycle fields. But I get only an empty
string in Client_notes. Is this because it is of type "text"? Does "text"
in a stored procedure require me to do something different?

Any help will be gratefully appreciated.
Nov 19 '05 #1
2 1713
Are you 100% certain that there is data in your variable???

Does it work if you run it manually in Query Analyzer?

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

"honcho" <go****@nospam.com> wrote in message
news:1121368686.306eb1d8d8bc6e793b8b236d5f174319@b ubbanews...
Hello,

Does anyone have an example of an SQL Server stored procedure that updates
a record, where one of its field is of type "text"? My procedure is

/*
** Update the client note and production cycle in a Sites record. Set
the
** When_submitted field to the current date/time.
*/
CREATE PROCEDURE spSiteNotePCycle
@ID int,
@Client_notes text,
@PCycle char(14)
AS
UPDATE Sites
SET Client_notes=@Client_notes, When_submitted={ fn NOW() },
PCycle=@PCycle
WHERE (ID = @ID)
GO

The procedure executes successfully, and after the update I get the
expected results in the When_submitted and PCycle fields. But I get only
an empty string in Client_notes. Is this because it is of type "text"?
Does "text" in a stored procedure require me to do something different?

Any help will be gratefully appreciated.

Nov 19 '05 #2
Yes, there is definitely text in the variable.
No, it does not work with Query Analyzer.
I got the answer from a different group yesterday. Instead of UPDATE, I
need to use UPDATETEXT or WRITETEXT. (These are described in Books Online.)
Thank you for your suggestions.

"Robbe Morris [C# MVP]" <in**@eggheadcafe.com> wrote in message
news:O3**************@tk2msftngp13.phx.gbl...
Are you 100% certain that there is data in your variable???

Does it work if you run it manually in Query Analyzer?

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

"honcho" <go****@nospam.com> wrote in message
news:1121368686.306eb1d8d8bc6e793b8b236d5f174319@b ubbanews...
Hello,

Does anyone have an example of an SQL Server stored procedure that
updates a record, where one of its field is of type "text"? My procedure
is

/*
** Update the client note and production cycle in a Sites record. Set
the
** When_submitted field to the current date/time.
*/
CREATE PROCEDURE spSiteNotePCycle
@ID int,
@Client_notes text,
@PCycle char(14)
AS
UPDATE Sites
SET Client_notes=@Client_notes, When_submitted={ fn NOW() },
PCycle=@PCycle
WHERE (ID = @ID)
GO

The procedure executes successfully, and after the update I get the
expected results in the When_submitted and PCycle fields. But I get only
an empty string in Client_notes. Is this because it is of type "text"?
Does "text" in a stored procedure require me to do something different?

Any help will be gratefully appreciated.


Nov 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Rob Wire | last post: by
5 posts views Thread by Alan T | 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.