커서를 사용하지 않는 각 행의 SQL Call Stored Proced Procedure

커서를 사용하지 않는 각 행의 SQL Call Stored Proced Procedure

표의 각 행에 대해 저장 프로시저를 호출하려면 어떻게 해야 합니까? 행의 열은 커서를 사용하지 않고 SP에 대한 입력 매개 변수입니다.

일반적으로 말하면, 저는 항상 세트 베이스의 어프로치를 찾고 있습니다(때로는 스키마를 변경해도 상관없습니다).

하지만, 이 단편은 그 가치가 있습니다.

-- Declare & init (2008 syntax)
DECLARE @CustomerID INT = 0

-- Iterate over all customers
WHILE (1 = 1) 
BEGIN  

  -- Get next customerId
  SELECT TOP 1 @CustomerID = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @CustomerId 
  ORDER BY CustomerID

  -- Exit loop if no more customers
  IF @@ROWCOUNT = 0 BREAK;

  -- call your sproc
  EXEC dbo.YOURSPROC @CustomerId

END

을 사용하다 예를 들어, 테이블을 순서대로 정렬합니다.의 ★★★ ID(AdventureWorks 용) the) )Sales.Customer샘플 테이블) 및 WHIT 루프를 사용하여 해당 고객에 대해 반복합니다.

-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0

-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT

-- select the next customer to handle    
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerID
ORDER BY CustomerID

-- as long as we have customers......    
WHILE @CustomerIDToHandle IS NOT NULL
BEGIN
    -- call your sproc

    -- set the last customer handled to the one we just handled
    SET @LastCustomerID = @CustomerIDToHandle
    SET @CustomerIDToHandle = NULL

    -- select the next customer to handle    
    SELECT TOP 1 @CustomerIDToHandle = CustomerID
    FROM Sales.Customer
    WHERE CustomerID > @LastCustomerID
    ORDER BY CustomerID
END

할 수 .어떤 종류의 할 수 .ORDER BY어떤 칼럼에.

DECLARE @SQL varchar(max)=''

-- MyTable has fields fld1 & fld2

Select @SQL = @SQL + 'exec myproc ' + convert(varchar(10),fld1) + ',' 
                   + convert(varchar(10),fld2) + ';'
From MyTable

EXEC (@SQL)

좋아요, 그럼 그런 코드를 실전 가동시키진 않겠지만, 당신의 요구는 충족시킬 수 있어요.

수용된 답변을 사용할 수도 있지만, 다른 방법으로는 테이블 변수를 사용하여 번호가 매겨진 값 세트(이 경우 테이블의 ID 필드만 해당)를 유지하고 행 번호별로 테이블에 JOIN을 사용하여 루프 내에서 작업에 필요한 모든 것을 가져올 수 있습니다.

DECLARE @RowCnt int; SET @RowCnt = 0 -- Loop Counter

-- Use a table variable to hold numbered rows containg MyTable's ID values
DECLARE @tblLoop TABLE (RowNum int IDENTITY (1, 1) Primary key NOT NULL,
     ID INT )
INSERT INTO @tblLoop (ID)  SELECT ID FROM MyTable

  -- Vars to use within the loop
  DECLARE @Code NVarChar(10); DECLARE @Name NVarChar(100);

WHILE @RowCnt < (SELECT COUNT(RowNum) FROM @tblLoop)
BEGIN
    SET @RowCnt = @RowCnt + 1
    -- Do what you want here with the data stored in tblLoop for the given RowNum
    SELECT @Code=Code, @Name=LongName
      FROM MyTable INNER JOIN @tblLoop tL on MyTable.ID=tL.ID
      WHERE tl.RowNum=@RowCnt
    PRINT Convert(NVarChar(10),@RowCnt) +' '+ @Code +' '+ @Name
END

마크의 답변은 훌륭합니다(어떻게 하면 좋을지 생각해 볼 수 있다면 코멘트를 하고 싶습니다.
'이렇게'를 하는 게 것 SELECT 번만 존재합니다(이 해야 하는 에는 이 이 필요했던 경우).SELECT매우 복잡해서 두 번 쓰는 것은 유지보수에 큰 문제가 되었습니다).

-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
SET @CustomerIDToHandle = 1

-- as long as we have customers......    
WHILE @LastCustomerID <> @CustomerIDToHandle
BEGIN  
  SET @LastCustomerId = @CustomerIDToHandle
  -- select the next customer to handle    
  SELECT TOP 1 @CustomerIDToHandle = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @LastCustomerId 
  ORDER BY CustomerID

  IF @CustomerIDToHandle <> @LastCustomerID
  BEGIN
      -- call your sproc
  END

END

저장된 프로시저를 테이블을 반환하는 함수로 변환할 수 있는 경우 교차 적용을 사용할 수 있습니다.

예를 들어, 고객 테이블이 있고 주문의 합계를 계산하려면 고객이 필요로 하는 함수를 만듭니다.ID와 합계를 반환했습니다.

이렇게 할 수 있습니다.

SELECT CustomerID, CustomerSum.Total

FROM Customers
CROSS APPLY ufn_ComputeCustomerTotal(Customers.CustomerID) AS CustomerSum

함수는 다음과 같습니다.

CREATE FUNCTION ComputeCustomerTotal
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT SUM(CustomerOrder.Amount) AS Total FROM CustomerOrder WHERE CustomerID = @CustomerID
)

위의 예는 단일 쿼리에서 사용자 정의 함수를 사용하지 않고도 수행할 수 있습니다.

단점은 기능이 매우 제한적이라는 것입니다. 즉, 저장 프로시저의 많은 기능을 사용자 정의 함수에서 사용할 수 없으며 저장 프로시저를 함수로 변환하는 것이 항상 작동하는 것은 아닙니다.

SQL Server 2005 이후에서는 CROSS APPLY 및 테이블 값 함수를 사용하여 이 작업을 수행할 수 있습니다.

• SQL Server 2005에서의 CROSS APPLY 사용

확실히 하기 위해 저장 프로시저를 테이블 밸류 함수로 변환할 수 있는 경우를 말합니다.

이는 이미 제공된 답변의 변형이지만 ORDER BY, COUNT 또는 MIN/MAX가 필요하지 않기 때문에 성능이 향상될 것입니다.이 접근방식의 유일한 단점은 모든 ID를 보유하기 위해 임시 테이블을 작성해야 한다는 것입니다(고객 목록에 공백이 있는 것을 전제로 합니다).아이디)

그렇긴 하지만, 나는 @Mark Powell의 의견에 동의하지만, 일반적으로 말하면, 세트 베이스의 어프로치가 여전히 더 나을 것이다.

DECLARE @tmp table (Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, CustomerID INT NOT NULL)
DECLARE @CustomerId INT 
DECLARE @Id INT = 0

INSERT INTO @tmp SELECT CustomerId FROM Sales.Customer

WHILE (1=1)
BEGIN
    SELECT @CustomerId = CustomerId, @Id = Id
    FROM @tmp
    WHERE Id = @Id + 1

    IF @@rowcount = 0 BREAK;

    -- call your sproc
    EXEC dbo.YOURSPROC @CustomerId;
END

이것은 위의 n3rds 솔루션의 변형입니다.MIN()이 사용되므로 ORDER BY를 사용하여 정렬할 필요가 없습니다.

고객님의 주의사항ID(또는 진행률에 사용하는 다른 숫자 열)에는 고유한 제약 조건이 있어야 합니다.또한 가능한 한 신속하게 고객님께 전달하기 위해ID를 인덱싱해야 합니다.

-- Declare & init
DECLARE @CustomerID INT = (SELECT MIN(CustomerID) FROM Sales.Customer); -- First ID
DECLARE @Data1 VARCHAR(200);
DECLARE @Data2 VARCHAR(200);

-- Iterate over all customers
WHILE @CustomerID IS NOT NULL
BEGIN  

  -- Get data based on ID
  SELECT @Data1 = Data1, @Data2 = Data2
    FROM Sales.Customer
    WHERE [ID] = @CustomerID ;

  -- call your sproc
  EXEC dbo.YOURSPROC @Data1, @Data2

  -- Get next customerId
  SELECT @CustomerID = MIN(CustomerID)
    FROM Sales.Customer
    WHERE CustomerID > @CustomerId 

END

먼저 임시 테이블에 배치하고 ID를 부여하여 검토해야 하는 일부 바카에 대해 이 방법을 사용합니다.

커서를 사용하지 않을 경우 외부에서 수행해야 합니다(테이블을 가져온 후 각 문에 대해 실행하며 매번 sp를 호출합니다). 커서를 사용하는 것과 같지만 SQL 외부에서만 가능합니다.왜 커서를 사용하지 않는 거죠?

행이 꽤 많을 때는 보통 이렇게 합니다.

1. SQL Management Studio를 사용하여 데이터 집합의 모든 sproc 매개 변수 선택
2. 오른쪽 클릭 -> 복사
3. 붙여넣기: Excel
4. 새 Excel 열에 '="EXEC schema.mysproc @schema=" & A2'와 같은 공식을 사용하여 단일 행의 SQL 문을 만듭니다(여기서 A2는 매개 변수가 들어 있는 Excel 열입니다).
5. SQL Management Studio에서 Excel 문 목록을 새 쿼리에 복사하고 실행합니다.
6. 다 했어요.

(대규모 데이터셋에서는 위에서 설명한 솔루션 중 하나를 사용합니다).

구분자 //

CREATE PROCEDURE setFakeUsers (OUT output VARCHAR(100))
BEGIN

    -- define the last customer ID handled
    DECLARE LastGameID INT;
    DECLARE CurrentGameID INT;
    DECLARE userID INT;

    SET @LastGameID = 0; 

    -- define the customer ID to be handled now

    SET @userID = 0;

    -- select the next game to handle    
    SELECT @CurrentGameID = id
    FROM online_games
    WHERE id > LastGameID
    ORDER BY id LIMIT 0,1;

    -- as long as we have customers......    
    WHILE (@CurrentGameID IS NOT NULL) 
    DO
        -- call your sproc

        -- set the last customer handled to the one we just handled
        SET @LastGameID = @CurrentGameID;
        SET @CurrentGameID = NULL;

        -- select the random bot
        SELECT @userID = userID
        FROM users
        WHERE FIND_IN_SET('bot',baseInfo)
        ORDER BY RAND() LIMIT 0,1;

        -- update the game
        UPDATE online_games SET userID = @userID WHERE id = @CurrentGameID;

        -- select the next game to handle    
        SELECT @CurrentGameID = id
         FROM online_games
         WHERE id > LastGameID
         ORDER BY id LIMIT 0,1;
    END WHILE;
    SET output = "done";
END;//

CALL setFakeUsers(@status);
SELECT @status;

이를 위한 더 나은 해결책은

1. 저장 프로시저의 복사/과거 코드
2. 해당 코드를 다시 실행할 테이블과 결합합니다(각 행).

이것은 깨끗한 테이블 형식의 출력을 얻을 수 있는 것입니다.모든 행에 대해 SP를 실행하는 경우 각 반복에 대해 보기 흉한 개별 쿼리 결과가 나타납니다.

순서가 중요한 경우

--declare counter
DECLARE     @CurrentRowNum BIGINT = 0;
--Iterate over all rows in [DataTable]
WHILE (1 = 1)
    BEGIN
        --Get next row by number of row
        SELECT TOP 1 @CurrentRowNum = extendedData.RowNum
                    --here also you can store another values
                    --for following usage
                    --@MyVariable = extendedData.Value
        FROM    (
                    SELECT 
                        data.*
                        ,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNum
                    FROM [DataTable] data
                ) extendedData
        WHERE extendedData.RowNum > @CurrentRowNum
        ORDER BY extendedData.RowNum

        --Exit loop if no more rows
        IF @@ROWCOUNT = 0 BREAK;

        --call your sproc
        --EXEC dbo.YOURSPROC @MyVariable
    END

저는 한 번에 20명의 직원만 처리할 수 있는 생산 코드를 가지고 있었습니다.코드의 골격은 다음과 같습니다.나는 단지 생산 코드를 복사하고 아래 것들을 제거했다.

ALTER procedure GetEmployees
    @ClientId varchar(50)
as
begin
    declare @EEList table (employeeId varchar(50));
    declare @EE20 table (employeeId varchar(50));

    insert into @EEList select employeeId from Employee where (ClientId = @ClientId);

    -- Do 20 at a time
    while (select count(*) from @EEList) > 0
    BEGIN
      insert into @EE20 select top 20 employeeId from @EEList;

      -- Call sp here

      delete @EEList where employeeId in (select employeeId from @EE20)
      delete @EE20;
    END;

  RETURN
end

결과 세트(표)에 대해 일련의 작업을 수행해야 하는 상황이 있었습니다.작전은 모두 정해진 것이니까 문제될 건 없지만...여러 곳에서 해야 했어요.따라서 관련 조각을 테이블 유형에 넣은 다음 각 결과 세트에 테이블 변수를 입력하면 SP를 호출하고 필요할 때마다 작업을 반복할 수 있습니다.

이것은 그가 묻는 정확한 질문에 대처하는 것은 아니지만 커서를 사용하지 않고 테이블의 모든 행에서 작업을 수행하는 방법에 대해 설명합니다.

@Johannes는 그의 동기에 대한 통찰력을 제공하지 않기 때문에 이것은 그에게 도움이 될 수도 있고 안 될 수도 있다.

저의 리서치는 이 잘 쓰여진 기사로 이어졌습니다.이 기사는 제 솔루션의 기초가 되었습니다.https://codingsight.com/passing-data-table-as-parameter-to-stored-procedures/

셋업은 다음과 같습니다.

    drop type if exists cpRootMapType 
go 

create type cpRootMapType as Table(
    RootId1 int 
    , RootId2 int
)

go 
drop procedure if exists spMapRoot2toRoot1
go 
create procedure spMapRoot2toRoot1
(
@map cpRootMapType Readonly
)
as

update linkTable set root = root1  
from linktable  lt 
join @map m on lt.root = root2

update comments set root = root1 
from comments c 
join @map m on c.root = root2

--  ever growing list of places this map would need to be applied....
--  now consolidated into one place 

구현은 다음과 같습니다.

... populate #matches

declare @map cpRootMapType 
insert @map select rootid1, rootid2 from #matches
exec spMapRoot2toRoot1 @map 

이것과 비슷한 것을 하고 싶다(커서를 사용하는 것과 매우 유사하지만).

[코드]

-- Table variable to hold list of things that need looping
DECLARE @holdStuff TABLE ( 
    id INT IDENTITY(1,1) , 
    isIterated BIT DEFAULT 0 , 
    someInt INT ,
    someBool BIT ,
    otherStuff VARCHAR(200)
)

-- Populate your @holdStuff with... stuff
INSERT INTO @holdStuff ( 
    someInt ,
    someBool ,
    otherStuff
)
SELECT  
    1 , -- someInt - int
    1 , -- someBool - bit
    'I like turtles'  -- otherStuff - varchar(200)
UNION ALL
SELECT  
    42 , -- someInt - int
    0 , -- someBool - bit
    'something profound'  -- otherStuff - varchar(200)

-- Loop tracking variables
DECLARE @tableCount INT
SET     @tableCount = (SELECT COUNT(1) FROM [@holdStuff])

DECLARE @loopCount INT
SET     @loopCount = 1

-- While loop variables
DECLARE @id INT
DECLARE @someInt INT
DECLARE @someBool BIT
DECLARE @otherStuff VARCHAR(200)

-- Loop through item in @holdStuff
WHILE (@loopCount <= @tableCount)
    BEGIN

        -- Increment the loopCount variable
        SET @loopCount = @loopCount + 1

        -- Grab the top unprocessed record
        SELECT  TOP 1 
            @id = id ,
            @someInt = someInt ,
            @someBool = someBool ,
            @otherStuff = otherStuff
        FROM    @holdStuff
        WHERE   isIterated = 0

        -- Update the grabbed record to be iterated
        UPDATE  @holdAccounts
        SET     isIterated = 1
        WHERE   id = @id

        -- Execute your stored procedure
        EXEC someRandomSp @someInt, @someBool, @otherStuff

    END

[/코드]

임시/변수 테이블의 ID나 IsIterated 열은 필요하지 않습니다. 루프를 반복할 때 컬렉션에서 상위 레코드를 삭제할 필요가 없도록 이 방법을 사용하는 것이 좋습니다.

언급URL : https://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor