4 Replies Latest reply: Jun 13, 2012 11:14 AM by Paul Nockolds RSS

    Dynamic SQL Statement

    Paul Nockolds

      Hi All

       

      QV11 SR1, SQL Server 2008

       

      I have a SQL Server select statement like the one below in QV...

       

      SELECT cmpname,
             Count (DISTINCT U.useremail) AS [OptIn Email]
      FROM   tbl_users U
      WHERE  U.cmpid IN ( 1838, 6, 1, 3, 1956, 4 )
             AND U.useroptinhbplemails = 1
      GROUP  BY cmpname
      ORDER BY cmpname
      ;
      

       

      i would like to pass in various numbers to part of the WHERE clause (part in bold above)

       

      i don't have the ability to create stored procedures on the server.

       

      Can i dynamically construct the SQL Statement within QV so that i could read in the numbers i want from a spreadsheet or variable and then execute the SQL ?  I don't want to write the same query loads of times with different numbers in that part of the WHERE clause if i can help it.

       

       

       

      Any ideas appreciated.

        • Re: Dynamic SQL Statement
          Mark Sheraton

          Paul,

           

          Something like this:

           

          Temp:

          LOAD * INLINE [

              Query, Numbers

              1, 1

              1,2

              2,4

              2,44

          ];

           

           

          Numbers:

          load Query

          ,Concat(Numbers,',') as QueryString

          Resident Temp

          Group by Query;

           

           

          Drop table Temp;

           

           

           

           

          Counts:

          LOAD Count(Query) as Y

          Resident Numbers;

           

           

          LET y = Peek('Y', 0) - 1;

          DROP Table Counts;

           

           

           

           

          // for each Query

          for k = 0 to $(y)

                              LET vQueryParamter = PEEK('QueryString', $(k),'Numbers');

           

                                        TEMP:

                                        SQL SELECT cmpname,

                                               Count (DISTINCT U.useremail) AS [OptIn Email]

                                        FROM   tbl_users U

                                        WHERE  U.cmpid IN ( '$(vQueryParamter)' )

                                               AND U.useroptinhbplemails = 1

                                        GROUP  BY cmpname

                                        ORDER BY cmpname

                                        ;

           

          next

           

           

           

          Mark