Quick Hints for using the RAISERROR Command
-
Have you got a script you could post for your 'DebugSP' proc? I am doing something now where this would be extremely useful? =)
-
Here is a conceptual look at one
All you need to do is fill in the bits. I've borrowed some of the stuff Dave talked about in his article and just layed out the concept for you.
Apply your own standards and away you go.
CREATE PROCEDURE lsp_Debug
(
@dtStart DATETIME
, @dtEnd DATETIME
-- Variable list here
)
AS
DECLARE @ReturnCode INT
BEGIN
-- Set the default return value to success
SELECT @ReturnCode = 0
IF EXISTS ( SELECT
1
FROM
debug_control
WHERE
dbg_can_debug = 1
)
BEGIN
-- Calculate time elapsed if you have an end date as well as a start date
-- Send message to event log so even if a rollback occurs we can see how far we got
DECLARE @sStringVar VARCHAR(440)
, @lInteger INT
, @fFloat DECIMAL(6,2)
, @byUnsignedInt TINYINT
SELECT
@sStringVar = 'A string'
, @lInteger = 44
, @fFloat = 3.14
, @byUnsignedInt = 56
RAISERROR('The string variable contains %s, the int contains %d, the float contains %d, the uint contains %d'
, 10 -- Informational severity only.
, 1
, @sStringVar
, @lInteger
, @fFloat
, @byUnsignedInt
) WITH LOG
, NOWAIT
INSERT dbo.debug_log
(
-- Add apropriate columns here
)
SELECT
-- Select approapriate data here
FROM
-- Use from if you want to peek at data being processed
-- Check for any errors
SELECT @ReturnCode = @@ERROR
END
-- Return any errors found along the way
RETURN @ReturnCode
END
GO
Hope you find this useful, sorry I don't have more time to flesh it out for you.
-
Thanks very much
-
Thanks for a useful and clear article - much appreciated.
One comment: as an initial step the SQL profiler can also be useful - no coding required! Of course you cannot see how the parameters flow through the procedure, but the various statements it executes may give an indication of the program flow.
-
excellect opps
-
Is it possible to log the RAISERROR messages into a table and not just to the Windows Even Log?
I've some stored procedures with the following line:
RAISERROR(@Message,10,1) WITH NOWAIT
and I'd like to have all this in a table without the need to modify it. So, the solution told by Steve about using a stored procedure like lsp_Debug couldn't be used.
Maybe in the way to execute it?
-
Richard Moldwin (11/23/2005)
T-SQL programming involves a major loss of productivity, compared to any other modern language I know of.I know it's a wicked old post but the comment above is just so very wrong. It's like anything else... you have to know how to use the tool correctly in order to be productive with it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply