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]
DECLARE @SelectSql AS NVARCHAR(4000);
DECLARE @sql AS NVARCHAR(4000);
DECLARE @StagingViewColumnName NVARCHAR(255);
DECLARE @columntype NVARCHAR(25);
DECLARE cols CURSOR
WHERE table_name = @stgTblnm
Fetch Next from cols into @StagingViewColumnName,@columntype;
WHILE @@FETCH_STATUS = 0
SET @sql = IIF(@StagingViewColumnName like '%%%','[vin].[udf_ParsePercentage][' + @StagingViewColumnName + ']',+ '[' + @StagingViewColumnName + ']');
SET @SelectSql = IIF(@StagingViewColumnName ISNUMERIC,'[vin].[udf_ParseNumber][' + @StagingViewColumnName + ']',+ '[' + @StagingViewColumnName + ']');
FROM cols INTO @StagingViewColumnName,@columntype;
-- EXEC ('
-- CREATE VIEW ' + @schemanm + '' + @VIEWNM + ' AS
--Select * from '+@stgTblNm+'
Thanks in advance for your kind help and suggestions.