USP_SMARTQUERYINSTANCE_CREATEORUPDATEIDSET

Parameters

Parameter	Parameter Type	Mode
@SMARTQUERYINSTANCEID	uniqueidentifier	IN
@TVFNAME	nvarchar(128)	IN
@TVFPARAMETERVALUES	nvarchar(max)	IN
@ISSTATIC	bit	IN
@USEINQUERYDESIGNER	bit	IN
@NUMROWS	int	IN
@CHANGEAGENTID	uniqueidentifier	IN
@IDSETREGISTERID	uniqueidentifier	INOUT
@TVFSCHEMA	nvarchar(128)	IN
Definition

Copy


CREATE procedure [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATEIDSET]
    @SMARTQUERYINSTANCEID [uniqueidentifier],
    @TVFNAME nvarchar(128),
    @TVFPARAMETERVALUES nvarchar(max),
    @ISSTATIC bit = 0,
    @USEINQUERYDESIGNER [bit] = 0,
    @NUMROWS [int] = null,
    @CHANGEAGENTID [uniqueidentifier] = null,
    @IDSETREGISTERID uniqueidentifier = null output,
  @TVFSCHEMA nvarchar(128) = 'dbo'

with execute as owner
as
set nocount on;

begin try

    -- update the IDSetRegister

    exec USP_IDSETREGISTER_CREATEORUPDATEFORSMARTQUERYINSTANCE @SMARTQUERYINSTANCEID, @ISSTATIC, @USEINQUERYDESIGNER, @NUMROWS, @CHANGEAGENTID, @IDSETREGISTERID output

    declare @FUNCTIONNAME as nvarchar(128)
    declare @PRIMARYKEYFIELD nvarchar(128)
    declare @OBJID as integer
    declare @STATICTABLE nvarchar(128)
    declare @CREATEFUNCTIONSQL as 
nvarchar(max)

    select @PRIMARYKEYFIELD = PRIMARYKEYFIELD from SMARTQUERYCATALOG
        inner join dbo.SMARTQUERYINSTANCE on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
    where SMARTQUERYINSTANCE.ID = @SMARTQUERYINSTANCEID;

    set @STATICTABLE 
= dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETSTATICTABLENAME(@SMARTQUERYINSTANCEID)

    set @FUNCTIONNAME = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETFUNCTIONNAME(@SMARTQUERYINSTANCEID)
    set @OBJID = null

    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 [' + @FUNCTIONNAME + ']()'
    else
        set @CREATEFUNCTIONSQL = 'create function [' + @FUNCTIONNAME + ']()'

    declare @DOCREATEFUNCTION bit

    if @ISSTATIC = 1
    begin
        set @CREATEFUNCTIONSQL = @CREATEFUNCTIONSQL + '
        returns table
        as
        return
        (select DISTINCT ID from dbo.[' + @STATICTABLE + '])'
        set @DOCREATEFUNCTION = 1
    end
    else
    begin
        set @CREATEFUNCTIONSQL = @CREATEFUNCTIONSQL + '
        returns table
        as
        return
        (
'

        set @CREATEFUNCTIONSQL = @CREATEFUNCTIONSQL +  'select DISTINCT ' + @PRIMARYKEYFIELD + ' ID from ' + @TVFSCHEMA + '.' + @TVFNAME + '(' + @TVFPARAMETERVALUES + '))';


        set @DOCREATEFUNCTION = 1;
    end



    if @DOCREATEFUNCTION = 1
    begin
        -- execute the sql to create or alter the function and grant rights

        exec (@CREATEFUNCTIONSQL)

        if @OBJID is null
        begin
            exec ('grant select on [' + @FUNCTIONNAME + '] to BBAPPFXSERVICEROLE');
            exec ('grant select on [' + @FUNCTIONNAME + '] to BBAPPFXREPORTROLE');
        end;

    end

end try

begin catch
    exec dbo.USP_RAISE_ERROR;
end catch