locked
How to create dynamic SQL sub string based on field name function id without writing static ? RRS feed

  • Question

  • User696604810 posted

    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 

    substring (DoneCode,@FunctionId,1)

    but i done know how to do that 

    so suppose tomorrow add new Function as 

    10,PCN 

    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
    (
    FunctionId int,
    FunctionName nvarchar(50)
    )
    insert into #Donecode
    values
    (1,'Lifecycle'),
    (2,'Rohs'),
    (3,'Reach'),
    (4,'FMD'),
    (5,'Parametric'),
    (6,'Package'),
    (7,'IntroductionDate'),
    (8,'MFG'),
    (9,'Qualification')
    
    
    create table #filedetails
    (
    FileID  int,
    DoneCode nvarchar(50)
    )
    insert into #filedetails (FileID,DoneCode) 
    values 
    (3301,'101011111110'),
    (3301,'101101111111'),
    (3301,'101001000011')
    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

    Tuesday, March 31, 2020 8:51 PM

All replies

  • User-1330468790 posted

    Hi ahmedbarbary,

      

    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.

    SQL:

    DEClARE @selectcontent NVARCHAR(MAX) =
        STUFF(
          (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 print @sql EXEC sp_executesql @sql

       

    STRING_AGG()

    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.

      

    Injection Risks

    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.

    Best regards,

    Sean

    Wednesday, April 1, 2020 8:34 AM