Hi All

I am using SQL Server 2005.

I have a table like this
CREATE TABLE dbo.trun_reports
(
    seq_id INT PRIMARY KEY,
    run_date TIMESTAMP,
    db2dbreport VARCHAR(MAX)
);

I have a Stored Procedure which calls some other stored procedures,
right now I do a Print statement at several points. Now I want to have
the whole thing in a string, so that I can display in a html page.

How do I...
1. Just insert the record once for each run of the StoredProcdure with
current date time
2. Keep appending strings to the db2dbreport field till the end of the
run.

Please let me know.

Thanks

TIMESTAMP has nothing to do with date or time.

(Microsoft, I have lost count of how many times I have hexed and cursed you
for this cruel choice of data type name.)

Better to do this:

CREATE TABLE dbo.trun_reports
(
    RunID INT IDENTITY(1,1) PRIMARY KEY,
    RunDate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
    Details VARCHAR(MAX)
);

CREATE PROCEDURE dbo.Run_Start
    @RunID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.trun_reports(Details) SELECT 'Started';

    SELECT @RunID = SCOPE_IDENTITY();
END
GO

CREATE PROCEDURE dbo.Run_Append
    @RunID INT,
    @LineItem VARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.trun_reports
        SET Details = Details + CHAR(13) + CHAR(10) + @LineItem
        WHERE RunID = @RunID;
END
GO

Now in your main procedure:

CREATE PROCEDURE dbo.whatever
    ...
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RunID INT;
    EXEC dbo.Run_Start @RunID = @RunID OUTPUT;
    ...do other stuff
    EXEC dbo.Run_Append @RunID, 'Got to step 2';
    ...do other stuff
    EXEC dbo.Run_Append @RunID, 'Got to step 3';
    ...do other stuff
    EXEC dbo.Run_Append @RunID, '...Finished';
END
GO

(And if you are expecting "print" statements to append to this table from
other stored procedures, you can add @RunID as a parameter to those stored
procedures, and call the same Run_Append stored procedure from there.  But
if other processes currently call those stored procedures, you better make
the parameter optional so that you keep the sp backward compatible with
other code.)

However, it might be much more useful to split the information into two
tables.  The reason is that in addition to the details about each step in
the run, you may also want to track the date/time that you hit each step.
This can be useful later in analyzing where the main procedure is slow, for
example.  And if that information is not useful to you now, you can just
ignore it, but I bet you will find it useful at some point.

A




<mod***@gmail.com> wrote in message
Show quoteHide quote

how to pass in a long string to stored procedure
Need Help With a Simple Query
Error converting data type varchar to numeric
Check int value
Strange characters with Bulk Load