USP_IDSET_CREATEORUPDATE

Parameters

Parameter	Parameter Type	Mode
@FUNCTIONNAME	nvarchar(128)	IN
@STATICTABLE	nvarchar(128)	IN
@STATIC	bit	IN
@IDCOLUMNTYPENAME	nvarchar(128)	IN
@SQLWITHCLAUSE	nvarchar(max)	IN
@SQL	nvarchar(max)	IN
@NUMROWS	int	IN
@CHANGEAGENTID	uniqueidentifier	IN
Definition

Copy


        CREATE procedure dbo.USP_IDSET_CREATEORUPDATE
        (
            @FUNCTIONNAME nvarchar(128),
            @STATICTABLE nvarchar(128),
            @STATIC bit = 0,
            @IDCOLUMNTYPENAME nvarchar(128),
            @SQLWITHCLAUSE nvarchar(max),
            @SQL nvarchar(max),
            @NUMROWS int = null,
            @CHANGEAGENTID uniqueidentifier = null
        )
        with execute as caller
        as
        set nocount on;

        declare @OBJID as int;
        declare @CREATEFUNCTIONSQL as nvarchar(max);

        declare @WITHASOFDATEOBJID as int;
        declare @FUNCTIONNAMEWITHASOFDATE as nvarchar(255);
        declare @CREATEFUNCTIONSQLWITHASOFDATE as nvarchar(max);

        set @FUNCTIONNAMEWITHASOFDATE = @FUNCTIONNAME + '_WITHASOFDATE';

        -- build the dynamic sql to create (or alter) the table-valued function containing the IDs and sequence for the given sql


        select @OBJID = id from dbo.sysobjects where id = object_id(N'[dbo].[' + @FUNCTIONNAME + ' ]') and type in (N'FN', N'IF', N'TF', N'FS', N'FT');

        if not @OBJID is null
            -- function already exists so use alter

            set @CREATEFUNCTIONSQL = 'alter function dbo.[' + @FUNCTIONNAME + ']()';
        else
            set @CREATEFUNCTIONSQL = 'create function dbo.[' + @FUNCTIONNAME + ']()';

        select @WITHASOFDATEOBJID = id from dbo.sysobjects where id = object_id(N'[dbo].[' + @FUNCTIONNAMEWITHASOFDATE + ' ]') and type in (N'FN', N'IF', N'TF', N'FS', N'FT');

        if not @WITHASOFDATEOBJID is null
            -- function already exists so use alter

            set @CREATEFUNCTIONSQLWITHASOFDATE = 'alter function dbo.[' + @FUNCTIONNAMEWITHASOFDATE + '](@ASOFDATE datetime)';
        else
            set @CREATEFUNCTIONSQLWITHASOFDATE = 'create function dbo.[' + @FUNCTIONNAMEWITHASOFDATE + '](@ASOFDATE datetime)';

        set @CREATEFUNCTIONSQLWITHASOFDATE = @CREATEFUNCTIONSQLWITHASOFDATE + '
        returns table
        as
        return
        ('

        if @STATIC = 1
            begin            
                set @CREATEFUNCTIONSQLWITHASOFDATE = @CREATEFUNCTIONSQLWITHASOFDATE + 'select DISTINCT ID from dbo.[' + @STATICTABLE + '])'
            end
        else
            begin

                set @CREATEFUNCTIONSQLWITHASOFDATE=@CREATEFUNCTIONSQLWITHASOFDATE + 
                ' 
                '
                +coalesce(@SQLWITHCLAUSE, '') + 
                '
                '

                set @CREATEFUNCTIONSQLWITHASOFDATE = @CREATEFUNCTIONSQLWITHASOFDATE + 'select DISTINCT QUERYRECID as ID '


                set @CREATEFUNCTIONSQLWITHASOFDATE = @CREATEFUNCTIONSQLWITHASOFDATE + ' from 
            (' + @SQL + ') as QRESULTS)'
            end

        -- execute the sql to create or alter the function and grant rights

        exec (@CREATEFUNCTIONSQLWITHASOFDATE)
        if @WITHASOFDATEOBJID is null
            exec dbo.USP_IDSET_GRANTRIGHTSTOFUNCTION @FUNCTIONNAMEWITHASOFDATE, 'select'

        set @CREATEFUNCTIONSQL = @CREATEFUNCTIONSQL + '
            returns table
            as
            return
            (select distinct ID from dbo.[' + @FUNCTIONNAMEWITHASOFDATE + '](getdate()) as QRESULTS)';

        -- execute the sql to create or alter the function and grant rights

        exec (@CREATEFUNCTIONSQL)
        if @OBJID is null
            exec dbo.USP_IDSET_GRANTRIGHTSTOFUNCTION @FUNCTIONNAME, 'select'