jeudi 18 février 2016

How to stop asp:SqlDataSource Query Builder from re-writing SQL (VS2005)

I simply paste this SQL code that I've carefully crafted in SQL Server Management Studio and it works.

SELECT   v.StudentID, v.StudentName, s.StudentHomeEmailAddress, s.StudentStudyYear, s.StudentMobilePhone, t.TradeName, p.ProgramName, (CASE WHEN s.StudentHomeEmailAddress IS NULL THEN 1 ELSE 0 END) AS Ord1
FROM     vwCurrentStudents AS v
         INNER JOIN tblStudents AS s ON s.StudentID = v.StudentID
         INNER JOIN tblStudentProgramReg AS r ON r.StudentID = s.StudentID AND r.StudProgEnrolStatusID IN (1, 3)
         INNER JOIN tblPrograms AS p ON r.ProgramID = p.ProgramID AND p.ProgramCatID IN (1, 3) AND p.ProgramID NOT IN (23, 112, 113)
         INNER JOIN tblTrades AS t ON t.TradeID = p.TradeID
WHERE    (@Who = 'Stu') 
  AND    (s.StudentStudyYear LIKE @StudyYear) 
  AND    (
          (CASE WHEN (SELECT Item FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1 OR dbo.fnISEMPTY(@Programs) = 1 OR @Programs = '%')
   AND    (CASE WHEN (SELECT Item FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t.TradeName))))) = t.TradeName THEN 1 ELSE 0 END = 1 OR dbo.fnISEMPTY(@Trades) = 1 OR @Trades = '%')
         )
ORDER BY Ord1, v.StudentName

However, if I paste it into the Query Builder, where it attempts to generate table links etc... the query changes to this!

SELECT     v.StudentID, v.StudentName, s.StudentHomeEmailAddress, s.StudentStudyYear, s.StudentMobilePhone, t.TradeName, p.ProgramName, 
                      (CASE WHEN s.StudentHomeEmailAddress IS NULL THEN 1 ELSE 0 END) AS Ord1
FROM         vwCurrentStudents AS v INNER JOIN
                      tblStudents AS s ON s.StudentID = v.StudentID INNER JOIN
                      tblStudentProgramReg AS r ON r.StudentID = s.StudentID AND r.StudProgEnrolStatusID IN (1, 3) INNER JOIN
                      tblPrograms AS p ON r.ProgramID = p.ProgramID AND p.ProgramCatID IN (1, 3) AND p.ProgramID NOT IN (23, 112, 113) INNER JOIN
                      tblTrades AS t ON t.TradeID = p.TradeID
WHERE     (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
                          (SELECT     Item
                            FROM          dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
                            WHERE      (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND 
                      (CASE WHEN
                          (SELECT     Item
                            FROM          dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
                            WHERE      (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) OR
                      (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
                          (SELECT     Item
                            FROM          dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
                            WHERE      (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) AND 
                      (dbo.fnISEMPTY(@Programs) = 1) OR
                      (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
                          (SELECT     Item
                            FROM          dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
                            WHERE      (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) AND 
                      (@Programs = '%') OR
                      (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
                          (SELECT     Item
                            FROM          dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
                            WHERE      (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND 
                      (dbo.fnISEMPTY(@Trades) = 1) OR
                      (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (dbo.fnISEMPTY(@Programs) = 1) AND (dbo.fnISEMPTY(@Trades) = 1) OR
                      (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (@Programs = '%') AND (dbo.fnISEMPTY(@Trades) = 1) OR
                      (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
                          (SELECT     Item
                            FROM          dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
                            WHERE      (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND 
                      (@Trades = '%') OR
                      (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (dbo.fnISEMPTY(@Programs) = 1) AND (@Trades = '%') OR
                      (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (@Programs = '%') AND (@Trades = '%')
ORDER BY Ord1, v.StudentName

And it does not work. It's wrong. The placements of ORs and ANDs are wrong.

Is there an option or something I can do to stop this?

My only other option is to paste it into the ASP code view directly and carefully make sure it's SQL compliant as it does not like line breaks, ampersands, etc

Aucun commentaire:

Enregistrer un commentaire