ansaurus

Question

Answer 1

A:

All those CASE statements make it look like a logistical nightmare. He should really brake it down into manageable chunks of SQL Code, which could then be placed in separate stored procedures.

Hopefully this will also enable him into seeing how he can simplify the SQL better. Far too many CASE statements in there for my liking.

You could do with going over the requirements spec for it to see why it was written in that way. I kind of think some metadata table could be used to help with the query, to help store some of the values which get checked in the CASE statements.

EDIT:

As you're now supporting it I would suggest trying to rewrite it without the dynamic SQL. This may help when it comes to you understanding whats going on as well as breaking it down into more manageable chunks.

kevchadders 2010-02-23 13:38:49

There is no more him. Some people coded this thig they call erp 10 years ago. And I am the new guy, which will be doing this kinda stuff everyday. I asked this question actually, to find out what to say to my manager if he tries to fire me :)

AIC 2010-02-23 13:40:36

and there is no documentation or requirements spec of this thing..

AIC 2010-02-23 13:43:03

I was about to ask about there being any specs for it... doh!

kevchadders 2010-02-23 13:44:13

If you'll be in charge of this code, I suggest you learn to love it and internalize it. It's your code now.

Wadih M. 2010-02-23 13:45:22

Answer 2

A:

you should try rewriting this using CLR stored procedures

scatman 2010-02-23 13:49:19

Answer 3

+1 A:

Rewriting would be the best solution. But, to try and debug it, use SQL Profiler (one of the SQL Server performance tools that comes with SQL Server) to put a stored procedure trace on the database and capture the exact command that executes this stored procedure. This will allow you to capture the exact parameter values being passed in and allow you to run it repeatedly in a SQL Management Studio window.

Then, you can modify the stored procedure to print out various values you wish to inspect at various points throughout the stored procedure in order to determine what is happening at any given point.

Alternatively, you can use visual studio to enable SQL debugging and actually step through the stored procedure.

NYSystemsAnalyst 2010-02-23 13:51:55

Visual Studio SQL debugging works on SQL Server 2008 and higher.

Wadih M. 2010-02-23 14:23:25

I thought it was SQL 2005 and higher?

NYSystemsAnalyst 2010-02-23 15:02:34

Answer 4

A:

1) at the top of the procedure, add an INSERT statement to log the input parameters into a table, with something like:

INSERT INTO YourLog (TextValue) VALUES ('EXEC BUTCEREP @SirketKod='''+COALESCE(@SirketKod,'NULL')+''', @IlkTarih='''+COALESCE(@IlkTarih,'NULL')+''', ...)

2) run this procedure several times from the application to capture the input parameters, including several good runs as well as the know problem.

3) add many PRINT statements all over the code. Even break down the long assignments into smaller, yet multiple assignments (where different parts are concatenated, the "+") and put prints between them:

...
PRINT @SqlMuh
...
PRINT @SqlMuh
...
PRINT @SqlGid
...

4) take the 'EXEC ...' strings from the log, and run the commands from SSMS, look at the print output and the code and determine what is going on.

KM 2010-02-23 13:56:25

Very interesting profiling technique. Thanks.

Wadih M. 2010-02-23 14:04:54

Answer 5

A:

Since you asked how you could potentially debug this:

Change the beginning from this:

  ALTER  PROC [dbo].[BUTCEREP] @SirketKod varchar(10), @IlkTarih varchar(10), @SonTarih varchar(10), @Tip int, @Tur int, @MasrafMrk varchar(100), @DovizKod varchar(5), @MaliTKod varchar(10)
    -- Tip 0-TarihAraligi; 1-2AylikButce; 2-YTDButce; 3-YTDCompPrev
    -- Tur 0-Muhasebe 1-Gider 2-Butce 3-ButceMuhasebe 4-ButceGider
    AS
      declare
        @SqlText nvarchar(4000),
        @SqlBud varchar(4000),
        @SqlMuh varchar(4000),
        @SqlGid varchar(4000),
        @xIlkT datetime,
        @xSonT datetime,
        @ThisMonth varchar(10),
        @NextMonth varchar(10),
        @ThisYear varchar(10),
        @PrevYear varchar(10)

Into this:

  declare @SqlText as nvarchar(4000)
  declare @SqlBud as varchar(4000)
  declare @SqlMuh as varchar(4000)
  declare @SqlGid as varchar(4000)
  declare @xIlkT as datetime
  declare @xSonT as datetime
  declare @ThisMonth as varchar(10)
  declare @NextMonth as varchar(10)
  declare @ThisYear as varchar(10)
  declare @PrevYear as varchar(10)

  set @ThisYear = 'your test value'
  set @NextMonth = 'your test value'
  set @ThisMonth = 'your test value'
  set @SqlText = 'your test value'
  set @SqlBud = 'your test value'
  set @SqlMuh = 'your test value'
  set @SqlGid = 'your test value'
  set @xIlkT = 'your test value'
  set @xSonT = 'your test value'
  set @PrevYear = 'your test value'

And debug at will. Append select statements in between sections like:

select @SqlMuh

To know their values at specific places.

Once you're happy with the changes you've done, go back and ALTER the SP. This will cut down the repetitive task of ALTERING-EXECUTING everytime you want to check something.

Wadih M. 2010-02-23 14:00:57

Answer 6

A:

I had a project like this last year and I found that the best thing to do was to create a table with a identity field and a varchar field and write the @SqlText variable to it just before you EXEC it. That way as you test and see results, you can get the latest SQL that was executed.

Long term, I agree with the majority of the people here that you should re-write this (although that's probably not a good short term solution unless this is the only SP that is like this).

Jeff Hornby 2010-02-23 14:21:31

everything is like this .. plus the deplhi code (which i am not a delphi programmer) ;)

AIC 2010-02-23 14:35:05

ansaurus

tags:

views:

answers:

What a StoredProcedure

related questions