Using Varchar(max) to add to a field?

Question

Using Varchar(max) to add to a field?

mick burden

SSCarpal Tunnel

Points: 4432
More actions
December 27, 2012 at 3:56 am

#268526

Merry Holidays everyone, I've created a basic table which includes a text field. I then created a SP which basically read the text field and then added to the same field, the problem is I get an error that says I can't use TEXT or Varchar(Max), is there a way round this?
Table
CREATE TABLE KLN
(RecId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Dte DATETIME,
machineid VARCHAR(50),
Userid VARCHAR(50),
memo text)
Stored Procedure
CREATE PROCEDURE [dbo].[Save_kln]
@sDte as datetime,
@smachineid as varchar(50),
@sUserid as varchar(50),
@smemo as text
AS
DECLARE @sRESULTS varchar(MAX)
if exists (select * from [kln] where [dte] = @sdte and Userid = @Suserid)
begin
set @sRESULTS = (select memo from [kln] where [dte] = @sdte and Userid = @Suserid)
update [kln] set memo = @sRESULTS + @smemo where [dte] = @sdte and Userid = @Suserid
end
else
begin
INSERT INTO [kln] (dte,machineid,userid,memo) VALUES (@sdte,@smachineid,@suserid,@smemo)
end

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You shouldn't be using TEXT in the first place, it's an old SQL 2000 data type, included only for backward compatibility. Use Varchar(max)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 2

GilaMonster (12/27/2012)
You shouldn't be using TEXT in the first place, it's an old SQL 2000 data type, included only for backward compatibility. Use Varchar(max)

Varchar(Max) isn't allowed in my situation either

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Post your code with the varchar(max) used and post the exact error message

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 4

turns out what you suggested did work after all, I'd like to thank you for your help