USP_CONSTITUENCYCODESELECTION_CREATEORUPDATE

CREATE procedure dbo.USP_CONSTITUENCYCODESELECTION_CREATEORUPDATE
(
    @CONSTITUENCYCODEID uniqueidentifier
)
as
    set nocount on;

    declare @FUNCTIONNAME nvarchar(256) = N'UFN_CONSTITUENCYCODESELECTION_' + replace(convert(nvarchar(36), @CONSTITUENCYCODEID), '-', '_');

    declare @IDSETREGISTERID uniqueidentifier = null;
    declare @SQL nvarchar(max);

    if (object_id(@FUNCTIONNAME) > 0)
    begin
        set @SQL = N'alter ';
        select
            @IDSETREGISTERID = ID
        from
            dbo.IDSETREGISTER
        where
            DBOBJECTNAME = @FUNCTIONNAME;
    end
    else
        set @SQL = N'create ';

    set @SQL = 
        @SQL + N' function dbo.' + @FUNCTIONNAME + N'() ' + nchar(13) +
        N'returns table ' + nchar(13) +
        N'as return ' + nchar(13) +
            N'/* This code was auto-generated by calling dbo.USP_CONSTITUENCYCODESELECTION_CREATEORUPDATE on ' + convert(nvarchar, getdate()) + N' */' + nchar(13) +
            N'select ID from dbo.UFN_CONSTITUENT_GETCONSTITUENTSBYCONSTITUENCYCODEID(''' + convert(nvarchar(36), @CONSTITUENCYCODEID) + N''');';

    exec sp_executesql @SQL;

    declare @CONSTITUENCYDESCRIPTION nvarchar(200) = dbo.UFN_CONSTITUENCY_GETDESCRIPTION(@CONSTITUENCYCODEID);
    declare @SELECTIONNAME nvarchar(600) = N'''' + @CONSTITUENCYDESCRIPTION + N''' Constituents';
    declare @DESCRIPTION nvarchar(2048) = N'All constituents who have the ''' + @CONSTITUENCYDESCRIPTION + N''' constituency.';

    declare @RECORDTYPEID uniqueidentifier;
    select
        @RECORDTYPEID = ID
    from
        dbo.RECORDTYPE
    where
        NAME = 'Constituent'

    if exists (select 1 from dbo.IDSETREGISTER where NAME = @SELECTIONNAME)
        set @SELECTIONNAME = @SELECTIONNAME + N' (User defined)';

    exec dbo.USP_IDSETREGISTER_CREATEORUPDATE @IDSETREGISTERID output, @SELECTIONNAME, @DESCRIPTION, @FUNCTIONNAME, 1, @RECORDTYPEID, 0, 0, null, null, null, 'Active constituencies';