Using stored procedure or not?

Using stored procedure or not?

• Gusbin

  Hi Louis, Thank you so much for your input!

  Frankly, I have to say that your suggested code does look much messier. But I suppose for keeping performance good, this is a step which I'll have to take.

  Your assumption regarding @v_ being variables is correct.

  The reason why I used a cursor was because I have to loop over multiple records. I don't see this in your code. You are saying something about 'letting SQL server optimizing the looping',... what do you exactly mean by that

  Thank you for the help

  
  

• psyjon

  It's not clear what the problem is. Are you saying the resultset is incorrect What is it you're trying to accomplish

  One problem i see is that you're selecting @v_planningitemsubtypeid, which isn't defined nor fetched in your cursor.

  
  

• srilalitha

  Hi Greg,

  Thank you for the response!

  I must say I wasn't very clear in my first message, sorry about that.

  Without going too much in detail: the main problem was that depending a number in 2 columns the output should be different. (looking up other data in other tables)

  But hooray, I managed to find it: Instead of using a stored procedure, I've created a function with 2 parameters. The great thing is I can do a select * from <function> I've created.

  However, I'm still not sure if I could have done the same in a stored procedure

  For completness of this thread I'll post the procedure here. I've to admit I didn't knew I could declare a RETURNS value with type table.

  CREATE FUNCTION test_with_cursor
  (
  @whenDate DateTime,
  @userid INTEGER
  )

  RETURNS @Results TABLE
  (
  timeconsumed int,
  description varchar(80)
  )

  AS
  BEGIN

  --declare some variables

  --declare the cursor

  --Open the cursor

  --fetch next record from cursor

  --iterate through cursor

  if @v_planningitemtypeid = 1 and @v_planningitemsubtypeid = 1
  begin

  --do this
  end

  if @v_planningitemtypeid = 1 and @v_planningitemsubtypeid = 2
  begin
  -- do this
  end

  if @v_planningitemtypeid = 2
  begin

  -- do this
  end

  if @v_planningitemtypeid = 3 and (@v_planningitemsubtypeid = 3 or @v_planningitemsubtypeid = 4 or @v_planningitemsubtypeid = 5)
  begin
  -- do this
  end

  if @v_planningitemtypeid = 4
  begin

  -- do this
  end

  
  --fetch next record
  END
  --close record

  RETURN
  END

  
  

• Haroon_S

  when you execute a statement like:

  select sum (column1 + column2)
  from table

  this is the same as a cursor over

  select column1, column2
  from table

  and in each iteration doing

  select @runningTotal = @runningTotal + @column1 + @column2

  Just in a more optimized way. Clearly whether this pertains to your exact situation depends on what happens in the <--do this> code of your function, but since the output is a single table, it is more than likely possible to do this in one statement. Can you share what --do this expands to

  
  
  

• KKL

  Generally speaking this is not really good for performance.. The different branches can be problematic for optimizing. But, if the data is not too large, maybe not a performance issue.

  I will say this, you could most likely build your entire loop, fetches, etc into a single select statement, depending on just how complex the branches are.

  Something along the lines of:

  insert into @results --assuming @v_ meant that it was a variable

  select case when planningitemtypeid = 1 and planningitemsubtypeid = 1
  then something
  when planningitemtypeid = 1 and planningitemsubtypeid = 2
  then something else

  ...........

  else blah end as timeconsumed,

  case when planningitemtypeid = 1 and planningitemsubtypeid = 1
  then something textual
  when planningitemtypeid = 1 and planningitemsubtypeid = 2
  then something else textual

  ...........

  else blah end as description

  FROM --most likely the rest of the query exactly like the CURSOR declaration

  It will look much messier, and will take wrapping your head around (if you don't immediately see it) but unless the calculations are just way out there (and if you can do it in a function that isn't likely) the savings might be great. The overhead of the declared cursor can be costly versus letting SQL Server optimize the looping...

  Just an idea.

  
  
  
• Using stored procedure or not?