• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

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?




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

Open in new window

0
Paula DiTallo
Asked:
Paula DiTallo
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]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

0
 
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 --
0
 
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.
0
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now