USP_CODETABLECATEGORY_GETORCREATECATEGORYID

create procedure dbo.USP_CODETABLECATEGORY_GETORCREATECATEGORYID
(
@DESCRIPTION nvarchar(100),
@ID uniqueidentifier output,
@CHANGEAGENTID uniqueidentifier =null
)
as
--Returns the ID for the CODETABLECATEGORY with the given description, or creates the category and returns the new id.

set nocount on;

set @ID=null;

select @ID=ID from dbo.CODETABLECATEGORY  where DESCRIPTION=@DESCRIPTION;

if @ID is null
begin

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;



    set @ID=newid();

    insert into dbo.CODETABLECATEGORY 
            (ID,DESCRIPTION,ADDEDBYID,CHANGEDBYID)
                values (@ID,@DESCRIPTION,@CHANGEAGENTID,@CHANGEAGENTID);

    if @@error<>0 return -7;

end

return 0;