lw_deleteActivityType.sql

If Exists (Select * From sysobjects Where name = N'lw_deleteActivityType' And user_name(uid) = N'dbo')
    Drop Procedure dbo.lw_deleteActivityType
Go

Create Procedure lw_deleteActivityType(
 @activityTypeId int
 )
As
 set nocount on 
 
 if exists(select * from activity a 
             join personActivity pa 
               on a.activityid= pa.activityId
                 where a.activityTypeId = @activityTypeId)
  begin
    raiserror('activitype used by person activity, cannot delete it.',-1,-1) with nowait
    return 0
  end
 else
  begin
    if exists(select * from activityType where activityTypeId = @activityTypeid)
      begin
       delete from  activityType where activityTypeid = @activityTypeid      
       return 0
      end
   else
      begin
       raiserror('activityType with %ld ID does not exist.',-1,-1,@activityTypeId) with nowait
       return 1
      end
    end
 
 

Go