Error in STORED PROCEDURE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error in STORED PROCEDURE

Author

Topic

richieWolf
Starting Member

7 Posts

Posted - 2008-07-13 : 21:19:48

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

josephsj_2000
Starting Member

1 Post

Posted - 2008-07-13 : 22:05:45

your @comments is a 'text' datatype and you're trying to concatenate it with a varchar. Its wrong

use @comments as varchar or nvarchar

text, ntext and image data types are cannot be declared as variables.

So always use varchar or nvarchar for such operations.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 00:39:15

Also make sure you specify a length whenever you're casting a value to varchar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources