create sql view from a sql function

Question

I have a function that will return all items in all firms.Table names should be parametric.But I could not create a view from that function.Because I can not return a table.The returned value is a string. Please help.Thanks.

GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation()
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function

      firm nvarchar(50) NULL 
)
AS 
BEGIN
DECLARE @referans AS INT, @NRP AS INT

DECLARE @TABLE AS NVARCHAR(MAX)
SET @TABLE = ''

DECLARE YourCursorNameHere CURSOR READ_ONLY
      FOR 
                    select c1.NR, C2.NR
                    from L_caPIFIRM c1 WITH(nolock)
                    INNER JOIN L_CAPIPERIOD C2 WITH(nolock) ON C1.NR=C2.FIRMNR 



OPEN YourCursorNameHere
FETCH NEXT FROM YourCursorNameHere INTO @referans, @NRP
WHILE @@FETCH_STATUS = 0
      BEGIN
                    IF @TABLE = '' 
                       SET @TABLE= 'SELECT FIRM=' + str(@referans) +', CODE FROM LG_' + SUBSTRING(('00'+ LTRIM(STR(@referans))),LEN(('00'+ LTRIM(STR(@referans))))-2,3)+ '_ITEMS'
                    ELSE
                       SET @TABLE= @TABLE + ' UNION SELECT FIRM=' + str(@referans) +', CODE FROM LG_' + SUBSTRING(('00'+ LTRIM(STR(@referans))),LEN(('00'+ LTRIM(STR(@referans))))-2,3)+ '_ITEMS'


      FETCH NEXT FROM YourCursorNameHERE INTO @referans,@NRP
      END
        -- EXEC( @TABLE)
CLOSE YourCursorNameHere
DEALLOCATE YourCursorNameHere

       --BEGIN
    --  INSERT INTO select 

    --  END



    RETURN;
END;
GO

Answer 1

Try to use dynamic sql .I didnt check it but maybe this is the way to do it...

 RETURNS @retContactInformation varchar(max)   -- not table
.
.


  DECLARE @sql nvarchar(max)
DECLARE @sqlx nvarchar(max)=dbo.ufnGetContactInformation()

set @sql='CREATE VIEW [aaa] 
AS
 '+ @sqlx+''
EXECUTE  sp_executesql @SQL

instead of cursor try while loop

CREATE TABLE #tempo (id INT IDENTITY (1,1),c1nr varchar(10),c2nr varchar(10))
insert into #tempo
select c1.NR, C2.NR

                    from L_caPIFIRM c1 WITH(nolock)
                    INNER JOIN L_CAPIPERIOD C2 WITH(nolock) ON C1.NR=C2.FIRMNR 
DECLARE @i int = (SELECT count(*) FROM #tempo)
DECLARE @id int=1
WHILE @i!=0
BEGIN
SELECT @referans=c1nr,
  @table =IIF ((@TABLE = ''),

'SELECT FIRM=' + str(@referans) +', CODE FROM LG_' + SUBSTRING(('00'+ LTRIM(STR(@referans))),LEN(('00'+ LTRIM(STR(@referans))))-2,3)+ '_ITEMS',

@TABLE + ' UNION SELECT FIRM=' + str(@referans) +', CODE FROM LG_' + SUBSTRING(('00'+ LTRIM(STR(@referans))),LEN(('00'+ LTRIM(STR(@referans))))-2,3)+ '_ITEMS' )
from #tmpo
where id=@id
set @id=@id+1
set @i=@i-1
END