Carrage Returns, Stored Procedures

Question:
What would be the best way to add carrage returns to a record, and would my
method create alot of overhead and wasted space. What would be the best
method to minimize overhead and wasted space.
Scenario:
Server MS SQL 2000
Table name= mitTickets
Fields= problem,details, created, lastupdate
UPDATE [mitTickets] SET [mitTickets].details= Now() &
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
n], [mitTickets].lastupdate = Now()
WHERE (((mitTickets.ID)=1));
Current Text in record [Data Example for mitTickets.Details]
Backup failed. Backup failure investigated and found tape ejected.
I would like to create a stored procedure that will append the current Date
and Time to each update that is being submitted via a web form. I only want
the web form to add text and have the stored procedure append the input text
the the existing record so that the data looks like the following. and have
it also update the field [lastupdate]
10/4/2003 9:10:32 AM
Tape inserted, backup completed successfully.
10/4/2003 7:15:02 AM
Backup failure investigated and found tape ejected.
10/4/2003 6:27:08 AM
Backup failed.
Thank You,
DaveAssuming it is a VARCHAR(8000),
CREATE PROCEDURE dbo.addDetails
(
@.mitTicketID INT,
@.detail VARCHAR(255)
)
AS
BEGIN
DECLARE @.append VARCHAR(512)
SET @.append = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ CONVERT(CHAR(10), GETDATE(), 101) + ' '
+ STUFF(RIGHT(CONVERT(VARCHAR(255), GETDATE(), 109), 14), 9, 4, ' ')
+ CHAR(13) + CHAR(10) + @.detail
UPDATE mitTickets
SET
details = details + @.append,
lastUpdate = GETDATE()
WHERE ID = @.mitTicketID
END
GO
You can change all that formatting to this, if you're happy with MMM DD
YYYY HH:MMAM format:
CONVERT(VARCHAR(26), GETDATE())
I don't know what you mean by overhead and wasted space; if you want a
carriage return in the column, there isn't a more efficient way than putting
a carriage return in the column. Not sure what kind of magic you might be
expecting there?
In any case, I think this should be redesigned. Why do you want to store
all that detail in a huge column? That column almost looks like a table.
Why don't you store detail rows in a relational table... it will make the
handling of adding rows much easier, it will make reporting easier, it will
certainly make *removal* of older data easier, it will avoid the problems
you'll have when you have to make it TEXT because all of your data reaches
8000 characters at some point, and it will completely eliminate the
redundancy of having a lastupdate column.
"new" <dduryea@.inetmicro.com> wrote in message
news:C0Xfb.50792$nU6.8338649@.twister.nyc.rr.com...
> Question:
> What would be the best way to add carrage returns to a record, and would
my
> method create alot of overhead and wasted space. What would be the best
> method to minimize overhead and wasted space.
> Scenario:
> Server MS SQL 2000
> Table name= mitTickets
> Fields= problem,details, created, lastupdate
> UPDATE [mitTickets] SET [mitTickets].details= Now() &
>
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
> n], [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.ID)=1));
>
> Current Text in record [Data Example for mitTickets.Details]
> Backup failed. Backup failure investigated and found tape ejected.
>
> I would like to create a stored procedure that will append the current
Date
> and Time to each update that is being submitted via a web form. I only
want
> the web form to add text and have the stored procedure append the input
text
> the the existing record so that the data looks like the following. and
have
> it also update the field [lastupdate]
> 10/4/2003 9:10:32 AM
> Tape inserted, backup completed successfully.
> 10/4/2003 7:15:02 AM
> Backup failure investigated and found tape ejected.
> 10/4/2003 6:27:08 AM
> Backup failed.
>
> Thank You,
> Dave
>|||Hi
I am not sure why you wish to add this to the database as a single field.
What would happend if you wanted to query the data e.g all activity on a
certain day?
It would be better to add the carriage returned on output, either when
selecting the data or probably more preferably through the user interface.
You can use the getdate() function to get the current system date and time.
John
"new" <dduryea@.inetmicro.com> wrote in message
news:C0Xfb.50792$nU6.8338649@.twister.nyc.rr.com...
> Question:
> What would be the best way to add carrage returns to a record, and would
my
> method create alot of overhead and wasted space. What would be the best
> method to minimize overhead and wasted space.
> Scenario:
> Server MS SQL 2000
> Table name= mitTickets
> Fields= problem,details, created, lastupdate
> UPDATE [mitTickets] SET [mitTickets].details= Now() &
>
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
> n], [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.ID)=1));
>
> Current Text in record [Data Example for mitTickets.Details]
> Backup failed. Backup failure investigated and found tape ejected.
>
> I would like to create a stored procedure that will append the current
Date
> and Time to each update that is being submitted via a web form. I only
want
> the web form to add text and have the stored procedure append the input
text
> the the existing record so that the data looks like the following. and
have
> it also update the field [lastupdate]
> 10/4/2003 9:10:32 AM
> Tape inserted, backup completed successfully.
> 10/4/2003 7:15:02 AM
> Backup failure investigated and found tape ejected.
> 10/4/2003 6:27:08 AM
> Backup failed.
>
> Thank You,
> Dave
>