July 13, 2008 at 7:23 pm
What I want to do is append the date on SQL SERVER to the @comments input parameter but I cannot use the Text datatype as a variable. How do I do it then?
CREATE PROCEDURE spUpdateComments
@userid int
@comments text
AS
DECLARE @newComment text
SET @newComment = @comments + CONVERT(varchar, GETDATE(), 8)
UPDATE Users
SET comments = @newComment
WHERE userId = @userid
July 13, 2008 at 7:37 pm
Use varchar(max) instead of text. Make sure you use .write to manipulate the varchar(max) datatype.
July 13, 2008 at 7:38 pm
Use varchar(max)
July 13, 2008 at 7:40 pm
Sorry, it's actually SQL Server 2000 so I can use that datatype. Is the SP actually correct or is there a better way to do it?
July 13, 2008 at 7:43 pm
The best thing I can tell you is to use varchar(8000). Are the comments larger than this? If so, the artilce I posted above shows how to manipulate the text data type also.
July 13, 2008 at 7:55 pm
I got it working using:
CREATE PROCEDURE spUpdateComments
@comments text,
@userid int
AS
DECLARE @newComment NVARCHAR(4000)
SELECT @newComment = CAST(@comments AS NVARCHAR(4000))
SET @newComment = @newComment + CONVERT(VARCHAR(19), GETDATE(), 120)
UPDATE Users
SET comments = @newComment
WHERE userid = @userid
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply