Carrage Returns, Stored Procedures

[new]

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, lupdate


Current Text in record [Data Example for mitTickets.Deta ils]
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.

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.

[new]

Correction Re: Carrage Returns, Stored Procedures

Sorry, I meant to post my Stored Procedure

UPDATE [mitTickets] SET [mitTickets].details= Now() &
Chr(13)+Chr(10) +[@detailstxtbox]+Chr(13)+Chr(10 )+Chr(13)+Chr(1 0)+[descriptio
n], [mitTickets].lastupdate = Now()
WHERE (((mitTickets.I D)=1));

"new" <dduryea@inetmi cro.com> wrote in message
news:sXWfb.5078 9$nU6.8336240@t wister.nyc.rr.c om...[color=blue]
> Question:
> What would be the best way to add carrage returns to a record, and would[/color]
my[color=blue]
> 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, lupdate
>
>
> Current Text in record [Data Example for mitTickets.Deta ils]
> Backup failed. Backup failure investigated and found tape ejected.
>
>
> I would like to create a stored procedure that will append the current[/color]
Date[color=blue]
> and Time to each update that is being submitted via a web form. I only[/color]
want[color=blue]
> the web form to add text and have the stored procedure append the input[/color]
text[color=blue]
> the the existing record so that the data looks like the following.
>
> 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.
>
>[/color]

[Erland Sommarskog]

Re: Correction Re: Carrage Returns, Stored Procedures

new (dduryea@inetmi cro.com) writes:[color=blue]
> Sorry, I meant to post my Stored Procedure
>
> UPDATE [mitTickets]
> SET [mitTickets].details = Now() & Chr(13) + Chr(10) + [@detailstxtbox] +
> Chr(13) + Chr(10) + Chr(13) + Chr(10) +
> [description],
> [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.I D)=1));[/color]

The CRLF are alright, but there are a coupld of other errors:

o There is no Now() in SQL Server. Use
convert(varchar (20), getdate, 121) to get the date.
o & is an operator for bitwise and. You want + for string concatenation.
o [@detailslistbox] will resolve to a column with the name
@detailslistbox . If you want refer to a variable, remove the brackets.
o While legal here, it is best to leave out the table name on the left-
hand side of the SET clause. You can only update the columns of one
table at a time, so the name is redundant here.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Manoj Rajshekar]

Re: Carrage Returns, Stored Procedures

Hi,

Use a trigger like the following to do this.

CREATE TRIGGER mitTickets_inse rted_time ON mitTickets
FOR insert
AS UPDATE mitTickets
SET lupdate= GETDATE()
WHERE problem in (SELECT problem FROM INSERTED)


That will take care of it.

Regards,
-Manoj