Last post Apr 01, 2020 08:34 AM by Sean Fang
Mar 31, 2020 08:51 PM|ahmedbarbary|LINK
How to create dynamic SQL sub string based on field name functioned without writing static ?
I have table name DoneCode i need when add new function on table Done code
automatically without rewrite or modify my code
so that i need to do
but i done know how to do that
so suppose tomorrow add new Function as
so no need to add new function in code as substring(DoneCode,10,1) as PCN
I need to use substring(DoneCode,FunctionId,1) as FunctionId
but i dont know how to make within loop
so can you help me
create table #Donecode
insert into #Donecode
create table #filedetails
insert into #filedetails (FileID,DoneCode)
select substring (Donecode,1,1)as Lifecycle,substring (Donecode,2,1)as Rohs,substring (Donecode,3,1)as Reach,substring (Donecode,4,1)as FMD,substring (Donecode,5,1)as Parametric,substring (Donecode,6,1)as Package,substring (Donecode,7,1)as IntroductionDate,substring (Donecode,8,1)as MFG,substring (Donecode,9,1)as Qualification
into #FunctionsDiv from #filedetails where DoneCode is not NULL and fileid=3301
drop table #filedetails
drop table #Donecode
drop table #FunctionsDiv
Apr 01, 2020 08:34 AM|Sean Fang|LINK
I think here is a workaround that you can use dynamic SQL as long as the function 'substring' would match the values for the modified data table.
DEClARE @selectcontent NVARCHAR(MAX) =
(SELECT ', substring(Donecode,' +convert(nvarchar(10),d.FunctionId) + ',1) AS ' + d.FunctionName
FROM [Donecode] d
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,'')
Declare @sql NVARCHAR(MAX) = 'SELECT ' + @selectcontent + ' from [filedetails] where DoneCode is not NULL and fileid=3301'
-- Check the sql statement
EXEC sp_executesql @sql
Note that the reason why I use 'Stuff' function here is that the SQL server version from my side is 2016. If you have sql server higher than version 2017, then you can use
STRING_AGG() to concatenate the string.
You should always bear in mind that using the dynamic SQL risks injection attack.
Although I can not see the risk in the code you gave, you should check if there is any risk in your read project.
Hope this can help you.