Are there any "gotchas" involved with commenting out print statements in tsql stored procedures?

Question

I am having problems with a report generated in SSRS producing different data than that datasource/dataset does otherwise, explained in detail here.

I found a suggestion on another post that "You may want to comment out your print statements and try that."

So I looked at the SP/dataset in question and see that it does have two Print statements:

Print(@Price)
. . .
Print(@SQLstring) 
Execute(@SQLstring)

I don't know what the advantage might be of having print statements like this in a SP in the first place. Are there any possible negative repurcussions from commenting them out?

UPDATE

When I tried to go ahead and "just do it" and alter the SP in Database Workbench (a product I heartily recommend, BTW, and have no affiliation with) by commenting out the two print statements, it told me I had a syntax error, but I don't know where it might be, as there are gobs of "("s in the SP. Here is what it told me:

Incorrect syntax near '('. while executing: ALTER Procedure [dbo].[sp_ViewPriceMatrix_Variance_RockBottom] . . .

Is there a T-SQL Lint online tool that could tell me which left paren is problematic? I googled for one, but couldn't find anything. The entire SP is:

ALTER Procedure [dbo].[sp_ViewPriceMatrix_Variance_RockBottom]
    @Unit varchar(4000),
    @BegDate datetime,
    @EndDate datetime,
    @SortBy varchar(20) 
AS
DECLARE 
@SQLstring varchar(max), 
@Statement varchar(8000), 
@ShortName varchar(50),
@ItemCode varchar(25), 
@PriceWeek varchar(30),
@LastPriceWeek varchar(30),
@Week int, 
@WherePriceWeek varchar(2000), 
@Price varchar(25),
@Contractprice int,
@CalendarBegDate datetime,
@CalendarEndDate datetime

ALTER table #Temp
(
    Unit varchar(50),
    ShortName varchar(25),
    ItemCode varchar(50),
    Description varchar(250),
    regionorder int,
    Contractprice varchar(50),
    Price varchar(25),
    Variance varchar(25),
    PriceWeek varchar(50),
    Week varchar(10)
)

-- create temp table
Select up.Unit, mm.ShortName,
up.ItemCode, Description=(Select Description from MasterProducts where ItemCode=up.itemcode), 
mm.regionorder, up.Contractprice 
into #TempContract From UnitProducts up 
Inner Join Unitmembers um on up.Unit=um.Unit and abs(um.pricesheet) = 1
Inner Join Members mm on um.memberno = mm.memberno
where up.Unit = @Unit 


Select @CalendarBegDate = C.BeginDate From Calendar C where @BegDate between C.BeginDate and C.EndDate
Select @CalendarEndDate = C.EndDate From Calendar C where @EndDate between C.BeginDate and C.EndDate

-- get weeks and where clause
SET @WherePriceWeek = ' Where '

Declare GetPriceWeek Cursor For
Select [PriceWeek] = C.Description, C.BeginDate 
From Calendar C 
where C.BeginDate <= @CalendarEndDate and C.EndDate >= @CalendarBegDate  
Order By 2 
Open GetPriceWeek
fetch next from GetPriceWeek into @PriceWeek, @BegDate
while @@fetch_status = 0
    begin       
        Select @Statement = ('Alter Table #TempContract Add [' + @PriceWeek + '] numeric(8,2) ')
        exec (@Statement)
        IF(@WherePriceWeek<>' Where ')
        Begin
            SET @WherePriceWeek = @WherePriceWeek + 'or '
        End
        SET @WherePriceWeek = @WherePriceWeek + 'IsNull(['+@PriceWeek+'],''0.00'') <> ''999.99'' '
        fetch next from GetPriceWeek into @PriceWeek, @BegDate
    end
Close GetPriceWeek
Deallocate GetPriceWeek


-- build member data by weeks
Declare GetMemberColumns Cursor For 
SELECT distinct ShortName,ItemCode
FROM #TempContract 
Order by ShortName,ItemCode
Open GetMemberColumns 
fetch next from GetMemberColumns into @ShortName,@ItemCode
while @@fetch_status = 0
    begin
        Declare GetMemberPrice Cursor For
        Select [PriceWeek] = C.Description, convert(varchar(20),
        cast(IsNull(mp.Price,0) as numeric(8,2))) as Price, 
        up.Contractprice 
        From MemberPrice mp 
        Inner Join UnitProducts up on mp.unit=up.unit and mp.itemcode=up.itemcode 
        Inner Join Unitmembers um on mp.memberno=um.memberno and mp.unit=um.unit and abs
(um.pricesheet) = 1
        Inner Join Members mm on mp.memberno = mm.memberno
        Inner Join Calendar C on mp.CYear=C.CYear and mp.Cweek=C.CWeek
        where mp.Unit = @Unit and C.BeginDate <= @CalendarEndDate and C.EndDate >= @CalendarBegDate 
and mm.ShortName = @ShortName and Mp.ItemCode = @ItemCode
        Open GetMemberPrice 
        fetch next from GetMemberPrice into @PriceWeek,@Price,@Contractprice
        while @@fetch_status = 0
            begin
                --Print(@Price)
                Select @Statement = ('Update #TempContract Set [' + @PriceWeek  + ']=''' + 
IsNull(@Price,'0.00') + ''' where ItemCode=''' + @ItemCode + ''' and Unit=''' + @Unit + ''' and 
[ShortName]=''' + @ShortName +'''')
                exec (@Statement)
                fetch next from GetMemberPrice into @PriceWeek,@Price,@Contractprice
            end
        Close GetMemberPrice
        Deallocate GetMemberPrice

        fetch next from GetMemberColumns into @ShortName,@ItemCode
    end
Close GetMemberColumns
Deallocate GetMemberColumns

--Select * From #TempContract 

-- final select statement
SET @Week = 0
SET @LastPriceWeek = ''
SET @SQLstring = ''

Declare SetPriceWeekSQL Cursor For
Select [PriceWeek] = C.Description, C.BeginDate 
From Calendar C 
where C.BeginDate between @CalendarBegDate and @CalendarEndDate 
Order By 2 
Open SetPriceWeekSQL
fetch next from SetPriceWeekSQL into @PriceWeek, @BegDate
while @@fetch_status = 0
    begin
        SET @Week = @Week + 1 
        IF(@SQLstring='')
        Begin
            SET @SQLstring = @SQLstring + 'Insert Into #Temp Select Unit, ShortName, ItemCode, 
Description, regionorder, Contractprice, IsNull('+
            '['+@PriceWeek+'],''0.00'') as Price, (convert(decimal(10,3),''-0.001'')) as 
Variance, 
            '''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From 
#TempContract'+@WherePriceWeek

            IF(@SortBy='Members')
            Begin
                SET @SQLstring = @SQLstring + ' UNION Select Unit, ShortName, '''', 
''zzzz'', '''', '''', ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar
(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
            End
            Else
            Begin
                SET @SQLstring = @SQLstring + ' UNION Select Unit, '''', ItemCode, 
Description, ''1000'', Contractprice, ''0'' as Price, ''-0.001'' as Variance, '''' as PriceWeek, 
''WK'+convert(varchar(2),@Week)+''' as Week From #TempContract'+@WherePriceWeek
            End
        End
        ELSE 
        Begin
            SET @SQLstring = @SQLstring + ' UNION '
            SET @SQLstring = @SQLstring + 'Select Unit, ShortName, ItemCode, Description, 
regionorder, Contractprice, IsNull('+
            '['+@PriceWeek+'],''0.00'') as Price, IsNull(convert(decimal(10,2),['+@PriceWeek
+'])-convert(decimal(10,2),['+@LastPriceWeek+']),''0.00'') as Variance, 
            '''+@PriceWeek+''' as PriceWeek, ''WK'+convert(varchar(2),@Week)+''' as Week From 
#TempContract'+@WherePriceWeek

            IF(@SortBy='Members')
            Begin
                SET @SQLstring = @SQLstring + ' UNION Select Unit, ShortName, '''', 
''zzzz'', '''', '''', ''0'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert(varchar
(2),@Week)+''' as Week From #TempContract Where IsNull(['+@LastPriceWeek+'],''0.00'') <> ''999.99'' or 
IsNull(['+@PriceWeek+'],''0.00'') <> ''999.99'' '
            End
            Else
            Begin
                SET @SQLstring = @SQLstring + ' UNION Select Unit, '''', ItemCode, 
Description, ''1000'', Contractprice, ''0'' as Price, ''0'' as Variance, '''' as PriceWeek, ''WK'+convert
(varchar(2),@Week)+''' as Week From #TempContract Where IsNull(['+@LastPriceWeek+'],''0.00'') <> ''999.99'' 
or IsNull(['+@PriceWeek+'],''0.00'') <> ''999.99'' '
            End
        End
        SET @LastPriceWeek = @PriceWeek
        fetch next from SetPriceWeekSQL into @PriceWeek, @BegDate
    end
Close SetPriceWeekSQL 
Deallocate SetPriceWeekSQL 

--Print(@SQLstring) 
Execute(@SQLstring)

Drop Table #TempContract 

IF(@SortBy='Members')
Begin
    Select 
        Unit,
        ShortName,
        ItemCode,
        Description,
        regionorder,
        Contractprice,
        convert(varchar(20),convert(decimal(10,2),Price)) as Price,
        sum(convert(money,Variance)) as Variance,
        VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert
(money,Variance)) From #Temp Where ShortName=T.ShortName and Week=T.Week) / Replace(((Select count
(regionorder) From #Temp Where ShortName=T.ShortName and Week=T.Week)-count(Variance)),'0','1'))), 
        PriceWeek,Week
    From #Temp T
    Group By
        Unit,
        ShortName,
        ItemCode,
        Description,
        regionorder,
        Contractprice,
        Price,
        PriceWeek,Week  
    Order By Week,ShortName,Description
End
ELSE
Begin
    Select 
        Unit,
        ShortName,
        ItemCode,
        Description,
        regionorder,
        Contractprice,
        convert(varchar(20),convert(decimal(10,2),Price)) as Price,
        sum(convert(money,Variance)) as Variance,
        VarianceAverage = convert(varchar(25),convert(decimal(10,2),(Select sum(convert
(money,Variance)) From #Temp Where ItemCode=T.ItemCode and Week=T.Week) / Replace(((Select count
(regionorder) From #Temp Where ItemCode=T.ItemCode and Week=T.Week)-count(Variance)),'0','1'))), 
        PriceWeek,Week
    From #Temp T 
    Group By
        Unit,
        ShortName,
        ItemCode,
        Description,
        regionorder,
        Contractprice,
        Price,
        PriceWeek,Week  
    Order By Week,Description,regionorder 
End

Drop Table #Temp

Your `ALTER table #temp` needs to be `CREATE table #temp`. You cannot create a table, temporary or otherwise, with the `ALTER` clause. — R. Richards, Jun 28 '16 at 19:32
Yes, I answered the man walking the streets with a lantern in his hand about that. — B. Clay Shannon-B. Crow Raven, Jun 28 '16 at 20:07

score 2 · Accepted Answer · answered Jun 28 '16 at 18:46

2

The Print statements are for debug. You can "just do it".

Change "ALTER table #Temp" for "CREATE table #Temp".

answered Jun 28 '16 at 18:46

Diógenes

79
1
6

This is very odd, but "ALTER table #Temp" does not actually appear in my SP; it appeared in the "err msg" returned by Database Workbench (which is what I copied), but the actual SP still says, "create table #Temp" – B. Clay Shannon-B. Crow Raven Jun 28 '16 at 18:51

Are there any "gotchas" involved with commenting out print statements in tsql stored procedures?

UPDATE

1 Answers1