T-SQL: Stored Procedure - variable assignment

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')

-- ##DEBUG
   IF (@dbgFlg = 'True')
     -- // 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






Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
that is because 'string' + NULL returns NULL ...

so, you need to handle NULL value in your case of building the SQL.

     SET @TestCaseStatement = 'UPDATE s2  SET ret_myDesignID = ' + CASE WHEN @ret_myDesignID IS NULL THEN 'NULL' ELSE CAST(@ret_myDesignID as varchar(10)) END + ' ' 

Open in new window

and in case the field is varchar:
     SET @TestCaseStatement = 'UPDATE s2  SET ret_myDesignID = ' + CASE WHEN @ret_myDesignID IS NULL THEN 'NULL' ELSE '''' + CAST(@ret_myDesignID as varchar(10)) + '''' END + ' ' 

Open in new window

Paula DiTalloIntegration developerAuthor Commented:
Thanks! BTW, on the second half of the question -- what happens under a goto with a return when processing a transaction--and is it possible to isolate the insert to the debug log outside of the transaction assumption that the insert to the debug logger is part of a specific transaction?  what i want to do is be able to use this goto from whatever transaction i happen to be on --
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.