May 26, 2003 at 6:31 am
I use the following code to record the time my procedure starts
DECLARE @PROC_START_ID int
INSERT INTO pricelist_trace (proc_start_time)
VALUES (GetDate())
SELECT @PROC_START_ID = (select max(exec_id) from mbsII..pricelist_trace)
......
-- Large number of commands
......
To record the time it finishes:
UPDATE mbsII..pricelist_trace SET proc_end_time = GetDate()
WHERE exec_id = @PROC_START_ID
Get an Error:
Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@PROC_START_ID'.
???
May 26, 2003 at 7:24 am
Hi roust_m,
quote:
I use the following code to record the time my procedure startsDECLARE @PROC_START_ID int
INSERT INTO pricelist_trace (proc_start_time)
VALUES (GetDate())
SELECT @PROC_START_ID = (select max(exec_id) from mbsII..pricelist_trace)
......
-- Large number of commands
......
To record the time it finishes:
UPDATE mbsII..pricelist_trace SET proc_end_time = GetDate()
WHERE exec_id = @PROC_START_ID
Get an Error:
Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@PROC_START_ID'.
Is there a GO within your large number of commands or somewhere else in your proc?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 26, 2003 at 7:46 am
Yes, several times...
May 26, 2003 at 7:55 am
Well, the only case I can reproduce your error, is when I use a GO statement.
I might be mistaken, but after a GO your declaration of the variable is out of scope, because GO ends the T-SQL batch. With DECLARE @var you declare only local variables.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 26, 2003 at 8:10 am
So, can I do the task (saving the number of the string of table, where the time of procedure start is recorded) any other way?
The structure of the table where I try to save start and finish time of a procedure:
CREATE TABLE [dbo].[pricelist_trace] (
[exec_id] [int] IDENTITY (1, 1) NOT NULL ,
[proc_start_time] [datetime] NULL ,
[proc_end_time] [datetime] NULL
) ON [PRIMARY]
May 26, 2003 at 8:24 am
hmmm,....never done this before at db level, I've always done somethings in my apps like
...
If Err.Number = 0 Then
do Update
Else
do not Update
End If
What about doing the update within a (nested) transaction? Update your data, go through your large number of commands and at the end if everything's ok you can use COMMIT to save your data.
Here's a snippet from BOL about Nested transations
CREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
BEGIN TRANSACTION OuterTran -- @@TRANCOUNT set to 1.
GO
INSERT INTO TestTran VALUES (1, 'aaa')
GO
BEGIN TRANSACTION Inner1 -- @@TRANCOUNT set to 2.
GO
INSERT INTO TestTran VALUES (2, 'bbb')
GO
BEGIN TRANSACTION Inner2 -- @@TRANCOUNT set to 3.
GO
INSERT INTO TestTran VALUES (3, 'ccc')
GO
COMMIT TRANSACTION Inner2 -- Decrements @@TRANCOUNT to 2.
-- Nothing committed.
GO
COMMIT TRANSACTION Inner1 -- Decrements @@TRANCOUNT to 1.
-- Nothing committed.
GO
COMMIT TRANSACTION OuterTran -- Decrements @@TRANCOUNT to 0.
-- Commits outer transaction OuterTran.
GO
Maybe someone has done this before or has a better idea?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 26, 2003 at 2:34 pm
Run the whole lot in a stored procedure!
May 29, 2003 at 7:28 pm
If you change what you are doing (in keeping with 540) from a lot of SELECT INTO * and UPDATE * to
DECLARE @PROC_START_ID int
INSERT INTO pricelist_trace (proc_start_time)
VALUES (GetDate())
SELECT @PROC_START_ID = (select max(exec_id) from mbsII..pricelist_trace)
EXEC spSelect
EXEC spUpdate
EXEC spInsert
EXEC spDelete
UPDATE mbsII..pricelist_trace SET proc_end_time = GetDate()
WHERE exec_id = @PROC_START_ID
You won't have the problem because the stored-procedure running all the nested sp's shouldn't lose focus.
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply