union по двум SP

← →
Ega23 © (2006-05-17 10:09) [0]

Всем привет!
MS SQL 2000

Есть SP, возвращает некий набор данных.
Надо вызвать её 2 раза (с разными параметрами) и полученные 2 набора данных "запихать" в один и передать на клиент.
В принципе, есть такой вариант - создаём временную таблицу с полями, как в SP, делаем в неё 2 раза insert из exec"а, на клиент передаём Select * from #tempTable.
Вариант не нравится тем, что набор данных, возвращаемый SP, ещё не устаканился - возможно будут добавлены некоторые поля или изменены существующие.
В двух местах это делать не хочется.

Собственно, вопрос: описаный мной вариант - наиболее приемлимый, или есть всё-таки что-то получше?

← →
Sergey13 © (2006-05-17 10:14) [1]

Если двойной результат будет нужен часто, то почему бы не переделать саму SP?

← →
ЮЮ © (2006-05-17 10:25) [2]

Предпочитаю в таких случаях UDF. Это же круто:

SELECT * FROM function(1)
UNION
SELECT * FROM function(2)

SELECT * FROM
function(2) f
JOIN Table t ON ...

← →
Ega23 © (2006-05-17 10:37) [3]

> Если двойной результат будет нужен часто, то почему бы не
> переделать саму SP?

Хм... 2 параметра сразу... А логику "внутре" реализовать...
Ты знаешь, пожалуй так и сделаю...

← →
Polevi © (2006-05-18 09:16) [4]

OPENROWSET

← →
Ega23 © (2006-05-18 09:28) [5]

> OPENROWSET

А не долго будет?

← →
Desdechado © (2006-05-18 11:39) [6]

А почему бы просто не объединить через UNION ALL ?
без всяких UDF

← →
ЮЮ © (2006-05-18 11:53) [7]

> Desdechado © (18.05.06 11:39) [6]
А почему бы просто не объединить через UNION ALL ?
без всяких UDF

знаеешь как? Скажи. Об этом и спрашивают: как объединить НД, возвращаемые ХП с двумя разнвми параметрами.

← →
Desdechado © (2006-05-18 12:25) [8]

а чем принципиально отличается
SELECT f1, f2 FROM t1 WHERE f3=:P1 UNION ALL SELECT f1, f2 FROM t1 WHERE f4=:P2
от
SELECT f1, f2 FROM proc1(:P1) UNION ALL SELECT f1, f2 FROM proc1(:P2)

← →
Ega23 © (2006-05-18 12:53) [9]

> А почему бы просто не объединить через UNION ALL ?

Потому, что в MS SQL, к сожалению, нельзя обращаться к ХП так, как к View.
Более того, если ХП, к примеру, возвращает НД из пяти столбцов, а мне для результирующей выборки нужно всего 3, то приходится создавать временную таблицу из пяти столбцов, имеющих типы данных и идущих в таком порядке, в каком возвращает их ХП, заливать данные из ХП в эту временную таблицу и уже из неё делать финальный select по трём столбцам.

← →
Ega23 © (2006-05-18 12:55) [10]

> SELECT f1, f2 FROM proc1(:P1)
> UNION ALL
> SELECT f1, f2 FROM proc1(:P2)

Приведи пример. Конкретный. В синтаксисе TSQL. Для MS SQL 2000.
Сумеешь - при личной встрече ящик пива выставлю.

← →
Desdechado © (2006-05-18 12:56) [11]

Ega23 © (18.05.06 12:53) [9]
кошмар какой
даже IB умеет это делать, не говоря уж об оракле

← →
Desdechado © (2006-05-18 12:59) [12]

Ega23 © (18.05.06 12:55) [10]
> Приведи пример Для MS SQL 2000
увы, MSSQL не владею, но всегда считал, что синтаксис там достаточно развитый
видимо, ошибался
а может, все-таки есть скрытые резервы?

> при личной встрече
сомневаюсь, что она состоится в обозримом будущем
слишком далеко

← →
Ega23 © (2006-05-18 13:03) [13]

> даже IB умеет это делать, не говоря уж об оракле

Увы. В MSSQL такое, к сожалению, невозможно. М.б. в 2005 и ввели, но его я ещё не "ковырял". Собственно, я ветку поэтому и организовал...

> сомневаюсь, что она состоится в обозримом будущем

Всяко бывает. Мир тесен.

← →
ЮЮ © (2006-05-18 13:18) [14]

А чем он не конкретный? proc1 - UDF, возвращающая DataSet

CREATE FUNCTION dbo.UeNotActualDepartments( @OnDate datetime, @Document int = -1, @ExcludeDocument int = 0 ) RETURNS @Res TABLE(Department int) -- Функция возвращает коды всех детей в дереве подразделений AS BEGIN INSERT INTO @Res SELECT od.Department FROM E_OldDepartments od JOIN E_DepartmentChanges dc ON od.DepartmentChange = dc.Id LEFT JOIN E_DepartmentDocuments dd ON dc.DepartmentDocument = dd.Id LEFT JOIN E_Documents doc ON dd.Document = doc.Id LEFT JOIN E_NewDepartments ren ON (od.Department = ren.Department) and (od.DepartmentChange = ren.DepartmentChange) WHERE ( (doc.Id IS NULL) OR (dd.DateFrom <= @OnDate) AND ( (@ExcludeDocument = 1) AND (doc.Id <> @Document) AND (doc.ApplicationType = 1) OR (@ExcludeDocument = 0) AND ((doc.ApplicationType = 1) OR (doc.Id = @Document)) ) ) AND (ren.ID IS NULL) order by od.Department RETURN END

Не лучший вариант, как оказалось, делать INSERT INTO @Res при значительных объемах. В приведеннов варианте он вообще лишний, т.к. все выбирается одним запросом и для этого имеется другой синтаксис UDF (смотри ниже). Но иногда, когда требуется много действий, курсор фетчить, то эта конструкция само то.

В следущей процедуре потребовались некоторые константы, так сказать на уровне бизнесс логики, поэтому применил эту же крнструкцию. Но в результате выбор из UDF сьал в 2 раза длинее, чем сам запрос, в ней выполнявшийся. Поэтому я поступил так: константы сделал параметрами, а над этой UDF обернул другую с аргументами-константами. вызов вложенных функций по времени оказался как и простое выполнение запроса. Итак

← →
ЮЮ © (2006-05-18 13:21) [15]

CREATE FUNCTION dbo._UsTheoryLoad_LoadWorkPlansInfo( @Year int, @Department int, @FactorVariant int, @IndependentStudyHoursSubActivity int, @IndependentStudyControl int, @CurrentConsulting int, @BeforeExamConsulting int ) RETURNS TABLE AS RETURN ( SELECT ISNULL(dg.ID, sg.ID) UGroup, f.ID Factor, wlhc.WorkLine, wlhc.SubActivity, wlhc.Hours, wlhc.Control, ISNULL( wpfs.ID, wpfc.ID) WorkPlanFactor, CASE WHEN MirrorWorkTermActivity = ExistWorkTermActivity THEN wl.StartDay ELSE wta.StartDay END StartDate, CASE WHEN MirrorWorkTermActivity = ExistWorkTermActivity THEN wl.Weeks ELSE wta.Weeks END Weeks, wt.WorkPlan, sta.Activity, (a.Course + wp.Year - a.Year) Course, ISNULL(ISNULL(wl.NAME, sd.Name), std.Name) Name , sh.WeekHours, sh.StudyDiscipline, sd.StudyPlan, sd.StandardDiscipline, sd.MasterLine, std.StandardPlan, std.Cycle, std.Component, pf.ID ParentFactor, wl.Department, f.Name FactorName, fac.CODE Budget, fac.Short_Name FacultyName, fac.Short_Name + " " + ISNULL(szql.CODE + "-" + sz.CODE, ql.CODE) + " " + ISNULL( szs.ShortName + " (" + CASE WHEN RIGHT(szql.CODE, 2) = "65" then "" ELSE " (" + szql.ShortName + "); " END + sz.ShortName + ")", ISNULL(s.ShortName, s.Name) + CASE WHEN RIGHT(ql.CODE, 2) = "65" then "" ELSE " (" + ql.ShortName + ")" END ) SpecialityName, ISNULL(wl.RoomHoursAreCalculated, sp.RoomHoursAreCalculated) RoomHoursAreCalculated, sp.StudyForm, ISNULL(ISNULL(wl.NAME, sd.Name), std.Name) + CASE WHEN Cycle = 5 THEN "(Факультатив)" ELSE CASE WHEN Component = 3 THEN "(Выбор)" ELSE "" END END DisciplineName, fvv.FactorValue FactorValue, pfvv.FactorValue ParentFactorValue, fvv.ForPeopleQuantity, fvv.ForTimePeriod, fpq.Name ForPeopleQuantityName, ftp.Name ForTimePeriodName, sg.ID StudyGroup, sg.Name GroupName, dbo.UuCount_UGroups_Budget(ISNULL(dg.ID, sg.ID)) BudgetCnt, dbo.UuCount_UGroups_Pay(ISNULL(dg.ID, sg.ID)) PayCnt, NULL SGroup FROM dbo.UsTheoryLoad_GetWorkTermActivities (@Year) tlwta LEFT JOIN (SELECT * FROM U_WorkLines wl Where Department = @Department) wl ON tlwta.ExistWorkTermActivity = wl.WorkTermActivity LEFT JOIN ( SELECT WorkLine, SubActivity, Hours, NULL Control FROM U_WorkHours UNION SELECT WorkLine, SubActivity, wl.CurrentHours - wlh.Hours * wl.Weeks Hours,Control FROM ( SELECT wh.WorkLine, @IndependentStudyHoursSubActivity SubActivity, Sum(wh.Hours) Hours, NULL Control FROM U_WorkHours wh LEFT JOIN U_WorkLines wl ON wh.WorkLine = wl.ID WHERE (wl.Department = @Department) and (Subactivity < 4) and ( (wl.RoomHoursAreCalculated IS NULL) OR (wl.RoomHoursAreCalculated = 0) ) GROUP By WorkLine HAVING Sum(Hours) > 0 ) wlh LEFT JOIN U_WorkLines wl ON wlh.WorkLine = wl.ID WHERE wl.CurrentHours - wlh.Hours * wl.Weeks > 0 UNION SELECT WorkLine, NULL, Quantity, Control FROM U_WorkControls ) wlhc ON wlhc.WorkLine = wl.ID +++++++++++++++++++++в форум столько не лезет +++++++++++++ LEFT JOIN S_FactorValues pfvv ON (pfvv.ID = ISNULL (pfvdn.ID, ISNULL(pfvd.ID, pfv.ID))) AND (pfvv.FactorVariant = @FactorVariant) AND (pfvv.FactorValue > 0) JOIN U_Groups sg ON (wt.WorkPlan = sg.WorkPlan) LEFT JOIN U_Groups dg ON (dg.UGroup = sg.ID) AND (sh.StudyDiscipline = dg.StudyDiscipline) WHERE (sa.ID IS NOT NULL) OR (c.ID IS NOT NULL) OR (f.ID = @IndependentStudyControl) )

функция-обертка:

CREATE FUNCTION dbo.UsTheoryLoad_LoadWorkPlansInfo( @Year int, @Department int, @FactorVariant int ) RETURNS TABLE AS RETURN ( SELECT * FROM dbo._UsTheoryLoad_LoadWorkPlansInfo( @Year, @Department, @FactorVariant, 10023, 85, 2, 11 ) )

Ну и использование:
Declare @Year int Declare @Department int Declare @FactorVariant int set @Year = 2006 set @Department = 16 -- каф АЯ set @FactorVariant = 2 Select * FROM dbo.UsTheoryLoad_LoadWorkPlansInfo(@Year, @Department, @FactorVariant) UNION Select * FROM dbo.UsTheoryLoad_LoadWorkPlansInfo(2005, @Department, @FactorVariant)

или

Select * FROM dbo.UsTheoryLoad_LoadWorkPlansInfo(@Year, @Department, @FactorVariant)n FULL JOIN dbo.UsTheoryLoad_LoadWorkPlansInfo(2005, @Department, @FactorVariant)o ON (o.UGroup = n.UGroup) AND (o.Factor = n.Factor) and (o.WorkLine = n.WorkLine) WHERE (o.UGroup IS NULL) OR (n.UGroup IS NULL)

Ну вот, пока примеры подыскивал и постил, за моим пивом уже пришли :)

UDF не дадут делать... :-(

Да и не такой уж жуткий запрос получается, сделал через #TempTable.
Но за идею - спасибо, на досуге обдумаю...

>UDF не дадут делать... :-(

а SP дают. Станно. Это же объекты практически одного плана, только одни PROCEDURE, а вторые - FUNCTION.

> а SP дают. Станно. Это же объекты практически одного плана,
> только одни PROCEDURE, а вторые - FUNCTION.

Должна оставаться совместимость с 7.0, а там функций нет.

если честно, когда говорят "ХП", то у меня даже не возникает вопроса - функция это или процедура
для меня это одно и то же (наверно, от IB осталось :)
поэтому и недоумение было

На самом деле у меня к TSQL достаточно много претензий, особенно после того, как я FB "пощупал".

union по двум SP Найти похожие ветки