USP_SIMPLEDATALIST_CREATEORUPDATE_SP

Create or update simple data list with a stored procedure implementation

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@NAME	nvarchar(60)	IN
@DESCRIPTION	nvarchar(1000)	IN
@PROCEDURE	nvarchar(128)	IN
@PARAMETERDEFXML	xml	IN
@TRANSLATIONFUNCTIONID	uniqueidentifier	IN
@SPECXML	xml	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SPECUINAME	nvarchar(60)	IN

Definition

Copy



CREATE procedure dbo.[USP_SIMPLEDATALIST_CREATEORUPDATE_SP]
    @ID uniqueidentifier = null output,
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @PROCEDURE nvarchar(128),
    @PARAMETERDEFXML xml = null,
    @TRANSLATIONFUNCTIONID uniqueidentifier,
    @SPECXML xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(60) = N''
as

set nocount on;

declare @ExistingID uniqueidentifier;
declare @OBJID int;

if @SPECUINAME is null
    set @SPECUINAME = N'';

if @ID is null
    begin
      select @ExistingID = ID from dbo.SIMPLEDATALISTCATALOG where [NAME] = @NAME;
      set @ID = @ExistingID;
    end
else
    select @ExistingID = ID from dbo.SIMPLEDATALISTCATALOG where ID = @ID;

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

-- make sure this is a valid procedure

select @OBJID = id from dbo.sysobjects where type in ('P', 'PC') and name = @PROCEDURE
if @OBJID is null
    begin
        raiserror ('Procedure ''%s'' does not exist or you do not have security access.', 16, 1, @PROCEDURE)
        return 1;
    end

declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();

if @ExistingID is null 

    begin
        if @ID is null set @ID = NewID();

        insert into dbo.SIMPLEDATALISTCATALOG (IMPLEMENTATIONTYPE,ID, [NAME], DESCRIPTION, PROCEDURENAME, PARAMETERDEFXML, TRANSLATIONFUNCTIONCATALOGID, SIMPLEDATALISTSPECXML, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SPECUINAME)
            values (0,@ID, @NAME, @DESCRIPTION, @PROCEDURE, @PARAMETERDEFXML, @TRANSLATIONFUNCTIONID, @SPECXML, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @SPECUINAME);
    end

else

    update dbo.SIMPLEDATALISTCATALOG 
    set [NAME] = @NAME,IMPLEMENTATIONTYPE=0,
        DESCRIPTION = @DESCRIPTION, 
        PROCEDURENAME = @PROCEDURE,
        PARAMETERDEFXML = @PARAMETERDEFXML,
        STATICPARAMETERDEFINITION = null,
        TRANSLATIONFUNCTIONCATALOGID = @TRANSLATIONFUNCTIONID,
        SIMPLEDATALISTSPECXML = @SPECXML,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE,
        SPECUINAME = @SPECUINAME
    where ID = @ExistingID;

return 0;