Как остановить Visual Studio Query Builder от переписывания SQL (VS2005)

Question

Я просто вставляю этот код SQL, который я тщательно обработал в SQL Server Management Studio, и он работает.

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

Однако, если я вставлю его в Query Builder, где он попытается сгенерировать ссылки на таблицы и т.д..., запрос изменится на этот!

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

И это не работает. Это не правильно. Места размещения ORs а также ANDs не правы.

Есть ли вариант или что-то, что я могу сделать, чтобы остановить это?

Единственный другой вариант - вставить его непосредственно в представление кода ASP и тщательно убедиться, что он совместим с SQL, поскольку не любит разрывов строк, амперсандов и т. Д.

Answer 1

Я решил это, отключив отображение макета таблицы в Query Builder. Код остается таким, каким вы его вставили. Спасибо @Ken за совет, такой же саркастичный, как и появился.