Sqlserver 2014: Not able to use table valued function inside dynamic query

Question

I have this table valued function which creates a table by splitting a string on the basis of delimiter.

USE [sysmon]
GO
/****** Object:  UserDefinedFunction [dbo].[ParseString3]    Script Date: 12/28/2016 3:49:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER FUNCTION [dbo].[ParseString3] (@String VARCHAR(MAX), @Delimiter VARCHAR(10))
RETURNS TABLE
AS

RETURN (
SELECT Ident, StringValue FROM 
    (
        SELECT Num as Ident,
            CASE 
                WHEN DATALENGTH(@delimiter) = 0 or @delimiter IS NULL
                    THEN SUBSTRING(@string, num, 1) 
            ELSE
                LTRIM(RTRIM(SUBSTRING(@String,
                    CASE 
                        WHEN (Num = 1 AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) <> @delimiter) THEN 1
                        ELSE Num + DATALENGTH(@delimiter)
                    END,
                    CASE CHARINDEX(@Delimiter, @String, Num + DATALENGTH(@delimiter))
                        WHEN 0 THEN LEN(@String) - Num + DATALENGTH(@delimiter)
                        ELSE CHARINDEX(@Delimiter, @String, Num + DATALENGTH(@delimiter)) - Num -
                            CASE 
                                WHEN Num > 1 OR (Num = 1 AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) = @delimiter) 
                                       THEN DATALENGTH(@delimiter)
                                ELSE 0
                            END
                       END
                    ))) 
              End  AS StringValue
        FROM sysmon.dbo.Numbers
        WHERE Num <= LEN(@String)
            AND (   
                    SUBSTRING(@String, Num, DATALENGTH(ISNULL(@delimiter,''))) = @Delimiter
                    OR Num = 1
                    OR DATALENGTH(ISNULL(@delimiter,'')) = 0 
                ) 
    ) R WHERE DATALENGTH(StringValue) <> 0
)

Now I am creating below dynamic query

declare @abc nvarchar(500)

       set  @abc = 'select distinct stringvalue, ' + [sysmon].[dbo].[feedmapping]('sungard') +
            'from ' + [sysmon].[dbo].[parseString3]('599362,16570,568838',',') +
            ' inner join dbname.dbo.tablename  
            on stringvalue = tablecolumn'
exec sp_executesql @abc

where feedmapping is another scalar valued function

USE [sysmon]
GO
/****** Object:  UserDefinedFunction [dbo].[FeedMapping]    Script Date: 12/28/2016 4:07:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FeedMapping](@feedName Varchar(20))
RETURNS VARCHAR(20)
AS
BEGIN
    DECLARE @ret VARCHAR(20)
    IF ( @feedname = 'SunGard')
        set @ret = 'SungardSymbol'
    ELSE IF ( @feedname = 'xxxx')
        set @ret = 'yyyyyyy'
    ELSE IF ( @feedname = 'aaaaa')
        set @ret = 'ccccccc'
    return @ret
END

while running it is giving me below error

Cannot find either column "sysmon" or the user-defined function or aggregate "sysmon.dbo.parseString3", or the name is ambiguous.

Please help

Answer 1

Dynamic SQL starts new session(SID). Your session with your current rights is not available in dynamic sql session. You do not have rights to object [sysmon].

One more thing. You can split string in far easy way. Try to use code:

CREATE FUNCTION [dbo].[str__split](
     @str           NVARCHAR(MAX)
    ,@delimiter     NVARCHAR(MAX)
)
RETURNS @split TABLE(
     [str] NVARCHAR(MAX)
)
AS
BEGIN

    INSERT INTO @split(
         [str]
    )
    SELECT 
        [X].[C].[value]('(./text())[1]', 'nvarchar(4000)')
    FROM 
        ( 
            SELECT 
                [X] = CONVERT(XML, '<i>' + REPLACE(@str, @delimiter, '</i><i>') + '</i>').query('.')
        )                   AS  [A] 
    CROSS APPLY 
        [X].[nodes]('i')    AS  [X]([C]);

   RETURN;

END

To solve your problem, I can recommend this:

1. create database user without login (proxy user) grant access rights
2. to proxy user on [sysmon] create stored procedure, dedicated to
3. dynamic sql call, and declare in header exec scope as proxy user.

Other, bad, but quick approach:

CREATE PROCEDURE dbo.sql_dynamic
WITH EXECUTE AS OWNER
--- your dynamic sql call ----

P.S. as cleared-out now, problem is in string concatenation with recordset.

Your first function returns string. It can be concatenated with remaining string. But second, table valued function returns recordset. You cannot concatenate string with recordset. Bad approach. You need to make aggregated string based on your table valued function recordset, and then concatenate it with your remaining string.

Answer 2

Try this way

DECLARE @sql      NVARCHAR(4000)= '',
        @col_name VARCHAR(128),
        @ps_input VARCHAR(8000)= '599362,16570,568838' 

SELECT @col_name = [sysmon].[dbo].[Feedmapping]('sungard')

SET @sql = '
SELECT DISTINCT stringvalue,' + @col_name+ '
FROM   [sysmon].[dbo].[Parsestring3](@ps_input, '','')
       INNER JOIN dbname.dbo.tablename
               ON stringvalue = tablecolumn'

EXEC Sp_executesql
  @sql,
  N'@ps_input varchar(8000)', -- Declaration 
  @ps_input=@ps_input  -- To pass the input inside dynamic query