Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MS SQL Server > Использование результата одной процедуры в другой

Автор: default_user 27.12.2006, 15:38

Доброго времени суток всем! smile

Я совсем немного времени работаю с MS SQL, у меня возникают некоторые затруднения, касательно написания хранимых процедур, т.к. логика их построения отличается от процедур на Interbase (с которым некоторый опыт имеется), по крайней мере, мне так кажется smile

Так вот. Возникла такая проблема. У меня есть процедура, которая возвращает некоторый набор данных. Далее, мне нужно использовать этот набор в другой процедуре как условие запроса. Каким образом можно это сделать, не используя промежуточных таблиц? И можно ли вообще объединить эти две процедуры в одну?

Мне нужен аналог оператора for select из Interbase (который делает какие-либо действия для каждой строки из выборки, в том числе и другие выборки).
Первая процедура (определяет пользователей, которых "видит" данный по статусу):

Код


CREATE PROCEDURE [dbo].[UsersAccess]
@Login varchar(50),
@Pass varchar(50)

 AS
declare @workgr int
declare @stat int
declare @user int

select @workgr=workgroup, @stat=status, @user=[user_id] from logins where [log]=@Login and pwrd=@Pass

if @stat=1
 begin
  select [user_id] from logins where [user_id]=@user
  return
 end

if @stat=2
 begin
  select [user_id] from logins where workgroup=@workgr
   return
 end

else
 begin
  select [user_id] from logins
  return
 end
GO

Во второй производится выборка и суммирование, которую нужно провести только для записей этих пользователей. То есть,  нужен либо цикл по каждому значению из первой процедуры (как???), либо условие where create_user in (*результирующий набор из процедуры 1*). Только как его сюда вписать этот набор? варианты
in (select [user_id]  from UsersAccess и in (execute UsersAccess @login=@ID, @pass=@Pswd)
не проходят   smile

Код


CREATE PROCEDURE [dbo].[SumByProjects]
@ID varchar(50)='',
@Pswd varchar(50)=''
 AS
select add_ind, sum(reestr.price_in) as in_sum, sum(reestr.price_out) as out_sum from
addition inner join reestr on 
(addition.project_id=reestr.project_id and addition.period_id=0 and addition.create_user in (???)
group by addition.add_ind
GO

Автор: SergKO 28.12.2006, 06:39

1 вариант: использовать

Код


INSERT #Table EXECUTE UsersAccess @Login, @Pass

2 вариант: оформить UsersAccess в виде функции

Код


CREATE FUNCTION [dbo].[UsersAccess] (@Login varchar(50),@Pass varchar(50))
RETURNS @UserId TABLE
(
    [user_id]    int
)
BEGIN 

declare @workgr int
declare @stat int
declare @user int
    
    select @workgr=workgroup, @stat=status, @user=[user_id] from logins where [log]=@Login and pwrd=@Pass
    
    if @stat=1 begin
        INSERT INTO @UserId
              select [user_id] from logins where [user_id]=@user
    end ELSE if @stat=2 begin
        INSERT INTO @UserId
          select [user_id] from logins where workgroup=@workgr
    end else begin
        INSERT INTO @UserId
          select [user_id] from logins
    end

RETURN
END
GO

и использовать в запросе

Код


select add_ind, sum(reestr.price_in) as in_sum, sum(reestr.price_out) as out_sum from
addition inner join reestr on 
(addition.project_id=reestr.project_id and addition.period_id=0 and addition.create_user in (SELECT user_id FROM dbo.UsersAccess(@Login, @Pass) )
group by addition.add_ind

Автор: default_user 28.12.2006, 10:08

Спасибо за совет! smile

Я вот пытаюсь реализовать это с помощью курсоров, но в виде функции будет работать быстрее, да?

И еще маленький вопрос. Как сделать, чтобы в результат не включался результирующий набор вызова какой-нибудь промежуточной процедуры? А то в query analizer я получаю два окна результатов.

Автор: SergKO 28.12.2006, 10:27

1. Да. Быстрее курсоров.
2. В queryanaliser'е Вы будете видеть столько наборов, сколько "попросили". Если у Вас появляются "лишние" наборы, значит где-то неверно используется эта самая промежуточная процедура.

Автор: default_user 28.12.2006, 10:44

Мои результаты smile

С функцией все работает, только условие нужно вписать не в join, а в секцию where после join. (Это я так, мало ли пригодится кому).
А вот первый вариант с #table что-то не получился smile

Ситнтакс проходит, а вот при исполнении пишет: Invalid object name '#Table'.
Может я чего недопонимаю? Объясните, плз, значение символа # smile

Наверное, я неправильно понимаю:

Код


CREATE PROCEDURE [dbo].[SumByProjects]
@Login varchar(50)='',
@Pass varchar(50)=''
 AS
INSERT  #Table EXECUTE UsersAccess @Login, @Pass
select add_ind, sum(reestr.price_in) as in_sum, sum(reestr.price_out) as out_sum from
addition inner join reestr on 
(addition.project_id=reestr.project_id and addition.period_id=0) where addition.create_user in (SELECT [user_id] FROM #Table) 
group by addition.add_ind
GO

Автор: SergKO 28.12.2006, 11:06

Перед выполнением конструкции

Код


INSERT  #Table EXECUTE UsersAccess @Login, @Pass

необходимо создать временную таблицу #Table (символ # говорит о том, что она временная)

Код


CREATE TABLE #Table ([user_id]    int)

т.е.

Код


CREATE PROCEDURE [dbo].[SumByProjects]
@Login varchar(50)='',
@Pass varchar(50)=''
 AS
CREATE TABLE #Table ([user_id]    int)
INSERT  #Table EXECUTE UsersAccess @Login, @Pass
select add_ind, sum(reestr.price_in) as in_sum, sum(reestr.price_out) as out_sum from
addition inner join reestr on 
(addition.project_id=reestr.project_id and addition.period_id=0) where addition.create_user in (SELECT [user_id] FROM #Table) 
group by addition.add_ind
GO

Добавлено @ 11:12
Кстати, если Вы посмотрите план запроса, то условие

Код


where addition.create_user in (SELECT [user_id] FROM #Table)

должно быть преобразовано оптимизатором в

Код


INNER JOIN #Table t ON t.user_id = addition.create_user

Тогда, сначала будет произведена выборка по этому условию, и только потом JOIN с reestr

Автор: default_user 28.12.2006, 11:37

SergKO,
Спасибо вам большое-пребольшое!!! smile