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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-22 : 23:24:34
|
Eric writes "I have a stored procedure that follows. I have one problem. In my DB I have the message field set as a text type, so that it is not limited in size. But in the SP, I check to see if the user exists before I do the insert, if the user does not exist then I want to add text to the insert first. The problem is that SQL Server does not allow a local variable to be a text type, and it will not allow me to add my local varchar variable to the text type SP's parameter. Can you help. I am using SQL Server 7.0 on an NT4 SP6 server. I do not want to limit the amount of text that can be used for this field, so how can I make this work? The only option I can find is to do the first insert and then maybe use the "updatetext" command. The problem there is that the updatetext command is not easy to use.
/*
PROCEDURE sp_saveNewMessage
@Created 02/19/2001
@Last Update 02/19/2001
@Author Eric Lebetsamer
@Description
This procedure is used to save a message that a user is sending to another user.
*/
CREATE Procedure sp_saveNewMessage
(
@fromUserID int,
@fromUserName varchar(12),
@toUserName varchar(12),
@subject varchar(40),
@message text
)
AS
Declare @toUserID int
Declare @messageText varchar(2500)
Select @toUserID = userID
From PCUsers
Where UserName = @toUserName And active = 1
If @@RowCount > 0
Begin
Insert Into PCMessages Values(GetDate(), 1, @fromUserID, @toUserID, @fromUserName, @toUserName, @subject, @message)
End
Else
Begin
Select @messageText = 'The message that you attempted to send to the user ' + @toUserName + ' could not be delivered. The user does not exist.'
+ Char(13) + Char(13) + Replicate('-', 50) + Char(13) + 'To : ' + @toUserName + Char(13) + 'From : ' + @fromUserName + Char(13) + 'Subject : '
+ @subject + Char(13) + 'Message Date : ' + Convert(varchar (25), GetDate()) + Char(13) + Char(13) + 'Message : ' + Char(13) + Convert(varChar(2200), @message)
Insert Into PCMessages Values(GetDate(), 1, 1, @fromUserID, 'SystemAdmin', @fromUserName, 'Message Not Deliverable', @messageText)
End " |
|
|
|
|
|
|
|
Dynamic local variable sizing in a SP or adding a text type and varchar type.