Can comments in stored procedures cause problems

Question

We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state for a long duration. I've ran this sp to track down what query is being ran by the processes and to my surprise, the following is what we discovered:

  --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', ''  
  --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725', 
  ... Several more executes that are commented out

Here's what our SP looks like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', ''  
  --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725', 
ALTER              PROCEDURE [SomeDB].[SP_POS_SU012_XML] @CRDATE        VARCHAR(08) ,
                    @CUS_CD     VARCHAR(10) ,
                    @PRODUCT_CD     VARCHAR(13) ,
                    @ORDER_GB   VARCHAR(03) ,
                    @CANCEL_YN      VARCHAR(01)  
                            ...

We weren't sure if the commented execute codes we defined for testing purposes in SP were actually being ran with the SP calls. From our internal testing, we couldn't reproduce the same behavior, but removing those commented lines of execute seemed to do the trick as sp_who2 no longed showed hundreds of processes stuck on SELECT.

But since I have absolutely no way to tell if this was the real cause or not, I have to ask: could such comments actually produce side-effects described here? Or worse, could this have happened from a deliberate attempt to attack our server/database?

score 2 · Answer 1 · answered Apr 16 '14 at 12:35

I've never heard of comments causing a problem.

The code you captured from the running server and the posted source are for different SPs. It makes me think that maybe the ..SU01 version is still in your database and being executed (from a job perhaps?) whereas you are trying to debug the ..SU012_XML code.

For testing you could add another INSERT to the SP, writing as single value to a dummy table. Then if the commented-out lines run there will be more rows in the dummy table than expected.

Can comments in stored procedures cause problems

1 Answers1