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
|