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



          Something like this:



          LOAD * INLINE [

              Query, Numbers

              1, 1








          load Query

          ,Concat(Numbers,',') as QueryString

          Resident Temp

          Group by Query;



          Drop table Temp;






          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');



                                        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