Concatenating Sql queries
-
Hi, I have a long SQL querie and my TL has asked that I concatenate them so I am not repeating the same querie over and over again. The thing is, each querie is inside a new for loop so if I concatenate, it is not picking up the previous query. For example this is what I had before for each for loop except that the end where clause is different for each.
for bla bla { ring sql = "select e.Firstname,e.Lastname,r.Role,r.Description,d.DivisionName,a.Applications,m.ManagerFirstname,m.ManagerLastname,t.ProgLanguagesDatabase" + " from employees e,Division d,Manager m,TechnicalSkills t,Role r,Applications a where " + " e.ManagerID = m.ManagerIDMy TL wants me to write for { sql1 = blabla } for bla { sql2 = sql1 + (Sql query) } Obviously it is telling me that sql2 does not exist. At the moment I have something like this after the queries. SqlDataAdapter myCommand = new SqlDataAdapter(sql, myConnection); and obviously I can't overload this each time with sql2, sql3 etc)??? Sianny aka Sharny -
Hi, I have a long SQL querie and my TL has asked that I concatenate them so I am not repeating the same querie over and over again. The thing is, each querie is inside a new for loop so if I concatenate, it is not picking up the previous query. For example this is what I had before for each for loop except that the end where clause is different for each.
for bla bla { ring sql = "select e.Firstname,e.Lastname,r.Role,r.Description,d.DivisionName,a.Applications,m.ManagerFirstname,m.ManagerLastname,t.ProgLanguagesDatabase" + " from employees e,Division d,Manager m,TechnicalSkills t,Role r,Applications a where " + " e.ManagerID = m.ManagerIDMy TL wants me to write for { sql1 = blabla } for bla { sql2 = sql1 + (Sql query) } Obviously it is telling me that sql2 does not exist. At the moment I have something like this after the queries. SqlDataAdapter myCommand = new SqlDataAdapter(sql, myConnection); and obviously I can't overload this each time with sql2, sql3 etc)??? Sianny aka SharnyRealistically, you should not be building SQL this way anyhow, you should be calling stored procs, from a data layer. I'm not sure I follow, you will have multiple queries in one call ? then your string needs to be defined outside the first loop, so you can add to it.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
Realistically, you should not be building SQL this way anyhow, you should be calling stored procs, from a data layer. I'm not sure I follow, you will have multiple queries in one call ? then your string needs to be defined outside the first loop, so you can add to it.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
if I declare them outside the loops string sql1; string sql2; string sql3; etc then inside each for loop I have a sql query like for () { sql2 = sql1 + "where bla bla"; } for() { sql3 = sql1 +"where bla bla"; } Obviously sql1 and 2 and 3 etc are never assigned as they are defined outside the loops and it never picks up their value? This is what the error is telling me anyway. Thanks
-
Realistically, you should not be building SQL this way anyhow, you should be calling stored procs, from a data layer. I'm not sure I follow, you will have multiple queries in one call ? then your string needs to be defined outside the first loop, so you can add to it.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
ah...i think I worked it out now. I'm not sure if that is what you were trying to tell me anyway, but I have declared the common query outside all the loops like this string sql = "Select e.Firstname,e.Lastname,r.Role,r.Description,d.DivisionName,a.Applications,m.ManagerFirstname,m.ManagerLastname,t.ProgLanguagesDatabase" + " from employees e,Role r,Division d, Manager m, TechnicalSkills t,Applications"; then later i have used sql2 = sql + "where etc"; Is this the correct way? thanks Sharny
-
ah...i think I worked it out now. I'm not sure if that is what you were trying to tell me anyway, but I have declared the common query outside all the loops like this string sql = "Select e.Firstname,e.Lastname,r.Role,r.Description,d.DivisionName,a.Applications,m.ManagerFirstname,m.ManagerLastname,t.ProgLanguagesDatabase" + " from employees e,Role r,Division d, Manager m, TechnicalSkills t,Applications"; then later i have used sql2 = sql + "where etc"; Is this the correct way? thanks Sharny
I expect each loop would end up adding a query to the main sql string, but, it's hard to say, I'm not entirely sure why you're putting strings in from more than one loop, or what the end product is to be. But yes, having one string to hold all the SQl was my suggestion.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
I expect each loop would end up adding a query to the main sql string, but, it's hard to say, I'm not entirely sure why you're putting strings in from more than one loop, or what the end product is to be. But yes, having one string to hold all the SQl was my suggestion.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )