How to select the columns data dynamically by stored procedure passing target as a parameter
-----
select datasourcetype, [source datatype], deltatype, sqltype, mysqltype, postgresqltype
from data.[sourceandtargetdatatypemapping] order by datasourcetype
---step 1
create view data.sorceinfotableview
as
select * from data.[sorceinfotable] where isactive = 1
------------------------
create proc getdynamiccolumndatatypes
@targetscript VARCHAR(200) = 'sql'
as begin
-- Variable
DECLARE @targttype VARCHAR(300) = 'b.' + @targetscript + 'type';
DECLARE @sql NVARCHAR(MAX);
drop table if exists ##temptabledata
SET @sql = '
SELECT a.SourceName as fnSourceName , a.datatype as fndatatype,concat(a.schemaname,a.tablename,
a.columnname) as joinlogic,
CASE
WHEN ''@targetscript'' = ''DELTA'' THEN
COALESCE(
CASE
WHEN @targttype = ''decimal'' THEN
CASE
WHEN COALESCE(NULLIF(a.precision, ''null''), ''0'') = ''0'' THEN
''decimal(34,17)''
ELSE ''decimal('' + CAST(COALESCE(NULLIF(a.precision, ''null''), ''0'')
AS VARCHAR) + '','' + CAST(COALESCE(NULLIF(a.scale, ''null''), ''0'') AS
VARCHAR) + '')''
END
ELSE @targttype
END,
''string'')
ELSE
COALESCE(
CASE
WHEN @targttype = ''decimal'' THEN
CASE
WHEN COALESCE(NULLIF(a.precision, ''null''), ''0'') = ''0'' THEN ''decimal(34,17)''
ELSE ''decimal('' + CAST(COALESCE(NULLIF(a.precision, ''null''), ''0'') AS VARCHAR) + '','' +
CAST(COALESCE(NULLIF(a.scale, ''null''), ''0'') AS VARCHAR) + '')''
END
WHEN @targttype IN (''varchar'', ''nvarchar'', ''ntext'', ''text'', ''string'') THEN
CASE
WHEN COALESCE(NULLIF(a.max_length, ''null''), ''0'') = ''0'' THEN ''varchar(1000)''
ELSE ''varchar('' + CAST(a.max_length AS VARCHAR) + '')''
END
ELSE @targttype
END,
''varchar(300)'')
END AS datatype
into ##temptabledata
FROM data.sorceinfotableview a
LEFT JOIN data.sourceandtargetdatatypemapping b ON a.SourceName = b.datasourcetype AND a.datatype = b.[source datatype];
';
-- Replace placeholders with actual values
SET @sql = REPLACE(@sql, '@targetscript', @targetscript);
SET @sql = REPLACE(@sql, '@targttype', @targttype);
-- Execute the dynamic SQL
EXEC sp_executesql @sql;
end
---------------
declare @type varchar(200) = 'sql'
--select @type
exec getdynamiccolumndatatypes @type
select * from ##temptabledata
--next delta
declare @type varchar(200) = 'delta'
--select @type
exec getdynamiccolumndatatypes @type
select * from ##temptabledata
----next mysql
declare @type varchar(200) = 'mysql'
--select @type
exec getdynamiccolumndatatypes @type
select * from ##temptabledata
---next postgresql
declare @type varchar(200) = 'postgresql'
--select @type
exec getdynamiccolumndatatypes @type
select * from ##temptabledata
