sql-server - 프로시저 - 프로 시저 호출 방법




저장된 프로 시저에서 임시 테이블을 반환하는 방법 (4)

CREATE PROCEDURE [test].[proc]
@ConfiguredContentId int,
@NumberOfGames int
AS
BEGIN
 SET NOCOUNT ON
 RETURN 
 @WunNumbers TABLE (WinNumb int)

    INSERT INTO @WunNumbers (WinNumb)
 SELECT TOP (@NumberOfGames) WinningNumber
 FROM [Game].[Game] g
 JOIN [Game].[RouletteResult] AS rr ON g.[Id] = rr.[gameId]
 WHERE g.[ConfiguredContentId] = @ConfiguredContentId
 ORDER BY g.[Stoptime] DESC

 SELECT WinNumb, COUNT (WinNumb) AS "Count"
 FROM @WunNumbers wn
 GROUP BY wn.[WinNumb]
END
GO

이 저장 프로 시저가 첫 번째 select 문에서 값을 반환하지만 두 번째 select 문의 값을 반환하고 싶습니다. Table @WunNumbers는 임시 테이블입니다.

어떤 아이디어 ???


먼저 템플릿으로 식별하고 템플릿을 SP에 심볼릭 링크하는 명명 규칙을 사용하여 반환 된 임시 테이블에 필요한 레이아웃이있는 실제 영구 테이블을 템플릿으로 만듭니다 (예 : tmp_SPName_Output) . 이 테이블에는 어떤 데이터도 포함되지 않습니다.

SP에서 동일한 이름 지정 규칙 (예 : #SPName_Output) 이있는 임시 테이블에 데이터를로드하려면 INSERT를 사용합니다. 그 존재 여부를 테스트하고 그렇지 않은 경우 오류를 리턴 할 수 있습니다.

sp를 호출하기 전에 다음을 사용하여 임시 테이블을 만듭니다.

SELECT TOP(0) * INTO #SPName_Output FROM tmp_SPName_Output;
EXEC SPName;
-- Now process records in #SPName_Output;

여기에는 다음과 같은 장점이 있습니다.

  • 임시 테이블은 ##과 달리 현재 세션에 대해 로컬이므로 다른 세션의 SP에 대한 동시 호출과 충돌하지 않습니다. 범위를 벗어나면 자동으로 삭제됩니다.
  • 템플릿 테이블은 SP와 함께 유지 관리되므로 출력이 변경되면 (예 : 새 열이 추가됨) SP의 기존 발신자가 중단되지 않습니다. 호출자는 변경할 필요가 없습니다.
  • 하나의 SP에 대해 서로 다른 이름으로 출력 테이블을 정의하고 모두 채울 수 있습니다. 또한 다른 이름을 사용하여 대체 출력을 정의하고 SP가 임시 테이블의 존재를 확인하여 채워야 할 대상을 확인하게 할 수 있습니다.
  • 마찬가지로 주요 변경 사항이 있지만 이전 버전과의 호환성을 유지하려는 경우 새 버전의 템플릿 테이블과 이름을 지정할 수 있지만 호출자가 만든 임시 테이블을 확인하여 이전 버전을 계속 지원할 수 있습니다.

어떤 버전의 SQL Server를 사용하고 있습니까? SQL Server 2008에서는 테이블 매개 변수 및 테이블 형식을 사용할 수 있습니다.

다른 방법은 사용자 정의 함수에서 테이블 변수를 반환하는 것이지만이 방법에 대한 큰 팬이 아닙니다.

여기 에서 예제를 찾을 수 있습니다.


임시 테이블을 호출자에서 작성한 다음 호출 된 SP에서 채울 수 있습니다.

  create table #GetValuesOutputTable(
     ...   
  );

  exec GetValues; -- populates #GetValuesOutputTable

  select * from #GetValuesOutputTable;

"insert exec"에 비해이 접근법의 몇 가지 이점은 중첩 될 수 있고 입력 또는 출력으로 사용될 수 있다는 점입니다.

일부 단점은 "인수"가 공용이 아니며 각 호출자 내에 테이블 생성이 존재하며 테이블의 이름이 다른 임시 객체와 충돌 할 수 있다는 것입니다. 임시 테이블 이름이 SP 이름과 거의 일치 할 때 도움이되며 일부 규칙을 따릅니다.

출력 전용 임시 테이블의 경우 삽입 점 실행 방식과 임시 테이블 접근 방식을 호출 된 SP에서 동시에 지원할 수 있습니다. 이것은 호출자에서 테이블을 정의 할 필요가 있지만 cmd 라인에서 테스트를 단순화하거나 외부 호출 할 때 도움이되므로 SP 연결에 너무 많은 도움이되지 않습니다.

  -- The "called" SP
  declare
      @returnAsSelect bit = 0;

  if object_id('tempdb..#GetValuesOutputTable') is null
  begin
      set @returnAsSelect = 1;
      create table #GetValuesOutputTable(
         ...   
      );
  end

  -- populate the table

  if @returnAsSelect = 1
      select * from #GetValuesOutputTable;

프로 시저의 반환 유형은 int입니다.

결과 집합을 반환 할 수도 있습니다 (현재 코드 에서처럼). (좋아, 문자열 인 메시지를 보낼 수도 있습니다.)

그게 당신이 할 수있는 유일한 "수익"입니다. 테이블 반환 매개 변수를 프로 시저 (BOL 참조)에 추가 할 수 있지만 입력 만 가능합니다.

편집하다:

(또는 언급 된 다른 포스터와 마찬가지로, 프로 시저가 아닌 테이블 값 함수를 사용할 수도 있습니다)







temp-tables