locked
Generate SQL query from dynamic tables, fields, operators, values RRS feed

  • Question

  • I have a requirement to build dynamic sql query from dynamic tables and their fields and values along with different conditions. Everything is dynamic.

    These fields are selected on UI  table1.id,table1.fieldname,table2city,table.postcode  - so this should be part of sql select statement. Along with that parameters also selected on UI, which insert into parameter table. Similarly, user can select any condition  - like, and, or, between,=,>,>=,<,<= etc

    How join happening which table go first? --based on seqno in parameter table

    In where condition 'And' came from operand table. If it is 'or' then it will come as a 'Or'

    create table metadata (id int, tablename varchar(100), fieldname varchar(100), datatype varchar(100))

    create table selectedfields(rulename varchar(10),fieldname varchar(100))

    create table parameter(paramname varchar(100), paramstring varchar(100),seqno int)

    create table operand(paramname varchar(100),conditional varchar(10))

    create table query (queryid int,paramname varchar(100),querystring varchar(100))

    insert into operand('rule1','and') insert into metadata values('table1','id,int) insert into metadata values('table1','fieldname','varchar') insert into metadata values('table1','fieldplace','varchar') insert into metadata values('table2','id,int) insert into metadata values('table2','city','varchar') insert into metadata values('table3','id,int) insert into metadata values('table3','country','varchar') insert into metadata values('table3','postcode','varchar')


    insert ino selectedfields values('rule1','table1.id')
    insert ino selectedfields values('rule1','table1.fieldname')
    insert ino selectedfields values('rule1','table2.city')
    insert ino selectedfields values('rule1','table3.postcode')

    insert into parameter values('rule1','table1.fieldname='test',1) insert into parameter values('rule1','table2.city=cal',2) insert into parameter values('rule1','table3.country='US',3) Output - in query table insert into query ('rule1', 'select table1.id,table1.fieldname,table2city,table3.postcode from table1 join table2 on table1.id=table2.id inner join table3 on table1.id=table3.id where

     table1.fieldname='test' and table2.city='cal' and table3.country='US''

    )









    • Edited by kdinuk Tuesday, December 4, 2018 6:35 PM
    Tuesday, December 4, 2018 6:05 PM

Answers

  • Hi kdinuk,

    Thanks for posting here.

    Per your request, I have done some research about it. However, some places are not quite clear. For example, join condition part. Therefore, the logic I could achieve is like this.

    Please refer:

    create table metadata (id int identity(1,1), tablename  varchar(100), fieldname  varchar(100), datatype  varchar(100))
    
    create table selectedfields(rulename varchar(10),fieldname varchar(100))
    
    create table parameter(paramname varchar(100), paramstring varchar(100),seqno int)
    
    create table operand(paramname varchar(100),conditional varchar(10))
    
    create table query (queryid int identity(1,1),paramname varchar(100),querystring varchar(100))
    
    insert into operand values('rule1','and');
    
    insert into metadata values('table1','id','int')
    insert into metadata values('table1','fieldname','varchar')
    insert into metadata values('table1','fieldplace','varchar')
    
    insert into metadata values('table2','id','int')
    insert into metadata values('table2','city','varchar')
    
    insert into metadata values('table3','id','int')
    insert into metadata values('table3','country','varchar')
    insert into metadata values('table3','postcode','varchar')
    
    
    insert into selectedfields values('rule1','table1.id')
    insert into selectedfields values('rule1','table1.fieldname')
    insert into selectedfields values('rule1','table2.city')
    insert into selectedfields values('rule1','table3.postcode')
    
    insert into parameter values('rule1','table1.fieldname=''test''',1)
    insert into parameter values('rule1','table2.city=''cal''',2)
    insert into parameter values('rule1','table3.country=''US''',3)
    
    /*
    --Output - in query table
    insert into query values 
    ('rule1',
    'select table1.id,table1.fieldname,table2city,table3.postcode from table1 join table2 on table1.id=table2.id inner join table3 on table1.id=table3.id 
    where table1.fieldname=''test'' and table2.city=''cal'' and table3.country=''US''')
    */
    
    --SQL Script
    
    SELECT 
    S.paramname,'SELECT '+S1.fields+' FROM '+S3.first_table+S2.join_condition+' WHERE '+S4.where_condition
    FROM operand S
    CROSS APPLY (select LEFT((select fieldname+',' from selectedfields where rulename=S.paramname for xml path('')),
    LEN((select fieldname+',' from selectedfields where rulename=S.paramname for xml path('')))-1) as fields) S1
    CROSS APPLY (select (select ' join '+substring(paramstring,1,CHARINDEX('.',paramstring)-1)+' on table1.id='+substring(paramstring,1,CHARINDEX('.',paramstring)-1)+'.id' from parameter where seqno!=1  and paramname=S.paramname for xml path(''))  as join_condition) S2
    CROSS APPLY (select substring(paramstring,1,CHARINDEX('.',paramstring)-1) as first_table from parameter where seqno=1  and paramname=S.paramname) S3
    CROSS APPLY (SELECT stuff((select T1.conditional+' '+T.paramstring+' ' from parameter T inner join operand T1 on T.paramname=T1.paramname
                  where T.paramname=S.paramname for xml path('')),1,3,'') as where_condition) S4
    
    --Output
    /*
    paramname                                                                                            
    ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    rule1                                                                                                SELECT table1.id,table1.fieldname,table2.city,table3.postcode FROM table1 join table2 on table1.id=table2.id join table3 on table1.id=table3.id WHERE  table1.fieldname='test' and table2.city='cal' and table3.country='US' 
    */
    

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kdinuk Thursday, December 20, 2018 10:46 AM
    Wednesday, December 5, 2018 6:06 AM

All replies

  • Hi kdinuk,

    Thanks for posting here.

    Per your request, I have done some research about it. However, some places are not quite clear. For example, join condition part. Therefore, the logic I could achieve is like this.

    Please refer:

    create table metadata (id int identity(1,1), tablename  varchar(100), fieldname  varchar(100), datatype  varchar(100))
    
    create table selectedfields(rulename varchar(10),fieldname varchar(100))
    
    create table parameter(paramname varchar(100), paramstring varchar(100),seqno int)
    
    create table operand(paramname varchar(100),conditional varchar(10))
    
    create table query (queryid int identity(1,1),paramname varchar(100),querystring varchar(100))
    
    insert into operand values('rule1','and');
    
    insert into metadata values('table1','id','int')
    insert into metadata values('table1','fieldname','varchar')
    insert into metadata values('table1','fieldplace','varchar')
    
    insert into metadata values('table2','id','int')
    insert into metadata values('table2','city','varchar')
    
    insert into metadata values('table3','id','int')
    insert into metadata values('table3','country','varchar')
    insert into metadata values('table3','postcode','varchar')
    
    
    insert into selectedfields values('rule1','table1.id')
    insert into selectedfields values('rule1','table1.fieldname')
    insert into selectedfields values('rule1','table2.city')
    insert into selectedfields values('rule1','table3.postcode')
    
    insert into parameter values('rule1','table1.fieldname=''test''',1)
    insert into parameter values('rule1','table2.city=''cal''',2)
    insert into parameter values('rule1','table3.country=''US''',3)
    
    /*
    --Output - in query table
    insert into query values 
    ('rule1',
    'select table1.id,table1.fieldname,table2city,table3.postcode from table1 join table2 on table1.id=table2.id inner join table3 on table1.id=table3.id 
    where table1.fieldname=''test'' and table2.city=''cal'' and table3.country=''US''')
    */
    
    --SQL Script
    
    SELECT 
    S.paramname,'SELECT '+S1.fields+' FROM '+S3.first_table+S2.join_condition+' WHERE '+S4.where_condition
    FROM operand S
    CROSS APPLY (select LEFT((select fieldname+',' from selectedfields where rulename=S.paramname for xml path('')),
    LEN((select fieldname+',' from selectedfields where rulename=S.paramname for xml path('')))-1) as fields) S1
    CROSS APPLY (select (select ' join '+substring(paramstring,1,CHARINDEX('.',paramstring)-1)+' on table1.id='+substring(paramstring,1,CHARINDEX('.',paramstring)-1)+'.id' from parameter where seqno!=1  and paramname=S.paramname for xml path(''))  as join_condition) S2
    CROSS APPLY (select substring(paramstring,1,CHARINDEX('.',paramstring)-1) as first_table from parameter where seqno=1  and paramname=S.paramname) S3
    CROSS APPLY (SELECT stuff((select T1.conditional+' '+T.paramstring+' ' from parameter T inner join operand T1 on T.paramname=T1.paramname
                  where T.paramname=S.paramname for xml path('')),1,3,'') as where_condition) S4
    
    --Output
    /*
    paramname                                                                                            
    ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    rule1                                                                                                SELECT table1.id,table1.fieldname,table2.city,table3.postcode FROM table1 join table2 on table1.id=table2.id join table3 on table1.id=table3.id WHERE  table1.fieldname='test' and table2.city='cal' and table3.country='US' 
    */
    

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kdinuk Thursday, December 20, 2018 10:46 AM
    Wednesday, December 5, 2018 6:06 AM
  • The design itself looks faulty to me

    Unless you're trying to create a query generator software I cant see any valid use cases for the above design

    Even within it there are lose ends like how do you determine what columns you use for join operation (in your example you've taken only id columns from the tables but if you see table1 and table3 there are additional columns too. So how will you determine what columns are to be joined? Keep in mind that we cant go by column names as there can be chance that column names can be different.

    So you need to clear tell us what your rules are

    In any case I feel the design itself looks a little overcomplicated


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, December 5, 2018 6:21 AM
  • Agree, you are right. Actually, we have requirement from business to create rules. Each rule is nothing but a select statrement query. To create a rule, user will select a table and their fields and values on UI by adding different conditions. INitially, we are staring with basics. Let's create a seperate table for joins to store columns to join. The data (table names, field names, conditions, from clause) will come UI and it should store in db. Based on that select statment should be build.

    I know you are expert. Please suggest me the design for such requirement. Thanks

    Wednesday, December 5, 2018 6:40 AM
  • Agree, you are right. Actually, we have requirement from business to create rules. Each rule is nothing but a select statrement query. To create a rule, user will select a table and their fields and values on UI by adding different conditions. INitially, we are staring with basics. Let's create a seperate table for joins to store columns to join. The data (table names, field names, conditions, from clause) will come UI and it should store in db. Based on that select statment should be build.

    I know you are expert. Please suggest me the design for such requirement. Thanks

    Unless I'm clear on the rules I cant suggest any solution

    You still didn't explain us how the join table will look like with some sample data

    Please repost entire sample data including the join table and then explain how you want query to be generated


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, December 5, 2018 6:56 AM
  • Here is my output - 'select table1.id,table1.fieldname,table2city,table3.postcode from table1 join table2 on table1.id=table2.id inner join table3 on table1.id=table3.id where  table1.fieldname='test' and table2.city='cal' and table3.country='US''

    Join will be stored in a seperate table. When a user select left/right join from UI, that values will be stored in table.

    Wednesday, December 5, 2018 8:28 AM
  • Here is my output - 'select table1.id,table1.fieldname,table2city,table3.postcode from table1 join table2 on table1.id=table2.id inner join table3 on table1.id=table3.id where  table1.fieldname='test' and table2.city='cal' and table3.country='US''

    Join will be stored in a seperate table. When a user select left/right join from UI, that values will be stored in table.

    I repeat

    Please post sample data from various tables including join like in your first post

    Then explain what you want as output


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, December 5, 2018 8:55 AM
  • Output data is inform of sql that you see in query table
    Wednesday, December 5, 2018 10:31 AM
  • Output data is inform of sql that you see in query table

    For such business rules, we couldn't exactly get your complete thought. Also, some important parts of the request is missing, and it is not related to technical feature problem.

    The best way of overcoming the problem is to communicate with experienced architects in face to face. It is hard to talk out a result based on current information.

    Thanks for your understanding.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 6, 2018 7:51 AM