Create Database VIEW dynamically

Hi All,

 I am having a situation where I need to create VIEW dynamically and apply udf on some of the columns of the VIEW.

We are ingesting a csv file where Column name keeps changing into a staging table where the datatype of all the columns are defined as nvarchar(max). Thereafter this staging table needs to be used as a driving table to create a VIEW. Since, the Column names are not static/fixed we need to create and populate the VIEW using dynamic sql embedded in a Stored proc.

Please find the structure of the staging table based on the source  files.

As you can see in the above screen shot the column names TeachingManhrs_India,CurrentBudgetMhrs_India and CurrentForecastMhrs_India has changed because the underlying source file was having the column names as TeachingManhrs_EMEA and Annex_BOG etc etc.

My requirement is to create a VIEW which will have the same column names which is there in the underlying staging table but I need to apply udf on some of the column names to parse the numbers and percentage.

Since the data type for the columns in the staging table is of nvarchar type.

I have started writing the Stored Proc but got stuck and therefore seek help in completing the stored proc Or if someone has a better option I m free to accept that as well.

Alter PROCEDURE [vin].[uspCreateView]
@VIEWNM VARCHAR(50), 
@stgTblNm nvarchar(max),
@schemanm nvarchar(max)
AS
Begin
DECLARE @SelectSql AS NVARCHAR(4000);
DECLARE @sql AS NVARCHAR(4000);
DECLARE @StagingViewColumnName NVARCHAR(255);
DECLARE @columntype NVARCHAR(25);

DECLARE cols CURSOR
FOR
SELECT column_name,Data_TYPE 
FROM information_schema.Columns
WHERE table_name = @stgTblnm 
and TABLE_SCHEMA=@schemanm;

Open cols
Fetch Next from cols into @StagingViewColumnName,@columntype;
WHILE @@FETCH_STATUS = 0
Begin
 SET @sql = IIF(@StagingViewColumnName like '%%%','[vin].[udf_ParsePercentage][' + @StagingViewColumnName + ']',+ '[' + @StagingViewColumnName + ']');
 SET	@SelectSql = IIF(@StagingViewColumnName ISNUMERIC,'[vin].[udf_ParseNumber][' + @StagingViewColumnName + ']',+ '[' + @StagingViewColumnName + ']');


  FETCH NEXT
   FROM cols INTO @StagingViewColumnName,@columntype;
END

CLOSE cols

DEALLOCATE cols
--    EXEC ('

--    CREATE VIEW ' + @schemanm + '' + @VIEWNM + ' AS
   
--(
--Select * from '+@stgTblNm+'
--)  ')


GO

Thanks in advance for your kind help and suggestions.

Regards

Vinny