Paula DiTallo
asked on
T-SQL: Stored Procedure - variable assignment
Techies--
Why does @TestCaseStatement in the code snippet fail to set to any value when variables are empty? I am trying to output to a table the values of variables whether they contain strings, integers or are just unset.
Also, when I get this to work, I want to import this logic into a program that has multiple transactions -- how do I manage
the GOTO routine so as to avoid the write to the error/debug log under ProcessDEBUG: from being treated as part of an existing transaction? Finally, what happens on a return in a GOTO when called while processing a transaction?
Why does @TestCaseStatement in the code snippet fail to set to any value when variables are empty? I am trying to output to a table the values of variables whether they contain strings, integers or are just unset.
Also, when I get this to work, I want to import this logic into a program that has multiple transactions -- how do I manage
the GOTO routine so as to avoid the write to the error/debug log under ProcessDEBUG: from being treated as part of an existing transaction? Finally, what happens on a return in a GOTO when called while processing a transaction?
DECLARE @dbgFlag bit
DECLARE @TestCaseStatement nvarchar(max)
SET @dbgFlag = 'True'
IF(@valTest = 'GO')
BEGIN
-- ##DEBUG
IF (@dbgFlg = 'True')
BEGIN
-- // this works because @StrikeTypeName is set to a value like 'Field'
-- SET @TestCaseStatement = 'UPDATE s1: StrikeTypeName incoming: ' +
-- @StrikeTypeName + ' conditioned lowercase: ' + @StrikeTypeName +
-- ' PatClassTypeID: ' + convert(varchar(10),@PatClassTypeID)
-- // This did NOT work, because the incoming values are not set to anything.
-- SET @TestCaseStatement = 'UPDATE s2: ret_myDesignID incoming: ' +
-- convert(varchar(10),@ret_myDesignID) + ' BOPatternNum incoming: ' +
-- @BOPatternNum + ' BOPatternID: ' + convert(varchar(10),@BOPatternID)
-- // This worked because I forced a value of '103'.
SET @TestCaseStatement = 'UPDATE s2: ret_myDesignID incoming: ' + convert(varchar(10),103)
PRINT @TestCaseStatement
GOTO ProcessDEBUG
END
END
ProcessDEBUG:
INSERT INTo SHG_ErrorLog
(EntryDate,
AppName,
ErrorMsg
)
VALUES
(getdate(),
'DASH',
@TestCaseStatement
)
return
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you have to ensure that your procedure does COMMIT/ROLLBACK need to be balanced.
so, if you have a BEGIN TRANSACTION, and RETURN without a COMMIT/ROLLBACK, you will have the result that if the calling code does not commit on it's turn, the transaction is rolled back.
so, for the debug, you can rollback explicitly (if a transaction has been started, check @@trancount value), and begin + commit a transaction for the debug stuff apart.
so, if you have a BEGIN TRANSACTION, and RETURN without a COMMIT/ROLLBACK, you will have the result that if the calling code does not commit on it's turn, the transaction is rolled back.
so, for the debug, you can rollback explicitly (if a transaction has been started, check @@trancount value), and begin + commit a transaction for the debug stuff apart.
ASKER