View Single Post
  #1 (permalink)  
Old February 20th, 2008, 05:31 AM
krshekhar krshekhar is offline
Registered User
 
Join Date: Feb 2008
Location: gurgoan, Hariyana, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to krshekhar Send a message via Yahoo to krshekhar
Default store procedure if else problem

i am writing a store procedure for rearranging the homepagedisplay order for an item in the item table as follows


create PROCEDURE sp_resetItemOrder(@oldOrder int, @newOrder int,@itmeId int)
as
declare @maxId int
IF @oldOrder=@newOrder/*IF THE OLD AND NEW ORDER ARE SAME*/
   return
else
if @newOrder=0/*IF THE NEW HOME PAGE ORDER IS 0 */

   begin
     update item set Item_HomePageorder=0 where item_Key=@itmeId
     update item set Item_HomePageorder=Item_HomePageorder-1 where Item_HomePageorder>@oldOrder
   end

else /*IF THE HOME PAGE ORDER IS NOT 0 */
  begin

    set @maxId=(select isnull(max(Item_HomePageorder),1) from item) /*SELECT THE MAX ITME HOME PAGE ORDER*/

   if @newOrder>=@maxId /*IF THE NEW ORDER IS GREATER THAN MAX ID */
      begin
       update item set Item_HomePageorder=Item_HomePageorder-1 where item_HomePageOrder between @oldOrder+1 and @maxId-1
       update item set Item_HomePageorder=@maxId where item_Key=@itmeId
      end
  else
    begin
      if @oldOrder>@newOrder
       begin

        update item set Item_HomePageorder=Item_HomePageorder+1 where Item_HomePageorder between @newOrder and @oldOrder-1
        update item set Item_HomePageorder=@newOrder where Item_Key=@itmeId
      end

     else
      begin
        update item set Item_HomePageorder=Item_HomePageorder-1 where Item_HomePageOrder>@oldorder and Item_HomePageorder<=@newOrder
        update item set Item_HomePageorder=@newOrder where Item_Key=@itmeId
      end

   end

  end


but it is not working properly
can any one sugges me whee i am wrong

SHEKHAR