sql server 프로그래밍 저장 프로 시저의 결과 집합에서 열 선택




sql 프로 시저 예제 (12)

80 열 및 300 행을 반환하는 저장 프로 시저가 있습니다. 나는 그 컬럼 중 2 개를 얻는 select를 작성하려고합니다. 좋아하는 것

SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'

위의 구문을 사용하면 오류가 발생합니다.

"잘못된 열 이름".

가장 쉬운 해결책은 저장 프로 시저를 변경하는 것이지만이를 작성하지 않았기 때문에 변경할 수 없다는 것을 알고 있습니다.

내가 원하는 것을 할 수있는 방법이 있습니까?

  • 결과를 넣을 임시 테이블을 만들 수 있지만 80 열이 있으므로 2 열만 가져 오려면 80 열 임시 테이블을 만들어야합니다. 반환 된 모든 열을 추적하지 않으려했습니다.

  • 내가 WITH SprocResults AS .... 사용하려고 WITH SprocResults AS .... 마크에 의해 제안 된,하지만 2 오류있어

    'EXEC'키워드 근처의 구문이 잘못되었습니다.
    ')'근처의 구문이 잘못되었습니다.

  • 테이블 변수 선언을 시도했는데 다음과 같은 오류가 발생했습니다.

    삽입 오류 : 제공된 값의 열 이름 또는 수가 표 정의와 일치하지 않습니다.

  • 시도하면
    SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
    오류가 발생했습니다.

    'exec'키워드 근처의 구문이 잘못되었습니다.


데이터의 수동 유효성 검사를 위해이 작업을 수행하는 경우 LINQPad를 사용하여이 작업을 수행 할 수 있습니다.

LinqPad에서 데이터베이스에 대한 연결을 만들고 다음과 비슷한 C # 문을 만듭니다.

DataTable table = MyStoredProc (param1, param2).Tables[0];
(from row in table.AsEnumerable()
 select new
 {
  Col1 = row.Field<string>("col1"),
  Col2 = row.Field<string>("col2"),
 }).Dump();

참조 http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet.aspx


원래 SP를 잘라내어 붙여 넣기하고 원하는 2를 제외한 모든 열을 삭제합니다. 또는. 결과 집합을 다시 가져와 적절한 비즈니스 개체에 매핑 한 다음 두 열을 LINQ로 가져옵니다.


쿼리를 분할 할 수 있습니까? 저장된 proc 결과를 테이블 변수 또는 임시 테이블에 삽입하십시오. 그런 다음 테이블 변수에서 2 열을 선택하십시오.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar


저장 프로 시저를 수정할 수 있으면 필요한 열 정의를 매개 변수로 쉽게 넣을 수 있으며 자동 생성 된 임시 테이블을 사용할 수 있습니다.

CREATE PROCEDURE sp_GetDiffDataExample
      @columnsStatement NVARCHAR(MAX) -- required columns statement (e.g. "field1, field2")
AS
BEGIN
    DECLARE @query NVARCHAR(MAX)
    SET @query = N'SELECT ' + @columnsStatement + N' INTO ##TempTable FROM dbo.TestTable'
    EXEC sp_executeSql @query
    SELECT * FROM ##TempTable
    DROP TABLE ##TempTable
END

이 경우 수동으로 임시 테이블을 만들 필요가 없습니다. 임시 테이블은 자동으로 생성됩니다. 희망이 도움이됩니다.


이것이 왜 그렇게 어려운지 아는 것이 도움이 될 수 있습니다. 저장 프로 시저는 텍스트 (print 'text') 만 반환하거나 여러 테이블을 반환하거나 테이블을 전혀 반환하지 않을 수 있습니다.

그래서 뭔가 SELECT * FROM (exec sp_tables) Table1 이 작동하지 않습니다.


이 작품은 나를 위해 : (즉, 난 단지 30 + sp_help_job 의해 반환 된 2 열 필요)

SELECT name, current_execution_status 
FROM OPENQUERY (MYSERVER, 
  'EXEC msdb.dbo.sp_help_job @job_name = ''My Job'', @job_aspect = ''JOB''');  

이 작업을하기 전에 다음을 실행해야했습니다.

sp_serveroption 'MYSERVER', 'DATA ACCESS', TRUE;

.... sys.servers 테이블을 업데이트합니다. (즉, OPENQUERY 내에서 자체 참조를 사용하는 것은 기본적으로 사용하지 않는 것 같습니다.)

필자의 간단한 요구 사항 때문에 Lance의 우수한 링크의 OPENQUERY 섹션 에 설명 된 문제 중 하나도 만나지 않았습니다.

Rossini, 동적으로 입력 매개 변수를 설정해야하는 경우 OPENQUERY를 사용하면 약간의 문제가 발생합니다.

DECLARE @innerSql varchar(1000);
DECLARE @outerSql varchar(1000);

-- Set up the original stored proc definition.
SET @innerSql = 
'EXEC msdb.dbo.sp_help_job @job_name = '''[email protected]+''', @job_aspect = N'''[email protected]+'''' ;

-- Handle quotes.
SET @innerSql = REPLACE(@innerSql, '''', '''''');

-- Set up the OPENQUERY definition.
SET @outerSql = 
'SELECT name, current_execution_status 
FROM OPENQUERY (MYSERVER, ''' + @innerSql + ''');';

-- Execute.
EXEC (@outerSql);

sp_serveroption 을 사용하여 기존 sys.servers 자체 참조를 직접 업데이트하는 것과 sp_addlinkedserver (Lance의 링크에 설명 된대로)를 사용하여 중복 / 별칭을 만드는 것의 차이점이 sp_serveroption 잘 모르겠습니다.

참고 1 : OPENQUERY가 proc 내에서 연결 문자열 정의를 요구하지 않는다면 OPENROWSET보다 OPENQUERY를 선호합니다.

참고 2 :이 모든 것을 말하면서 : 일반적으로 INSERT ... EXEC를 사용할 것입니다. 예, 10 분 여분의 타이핑입니다. 그러나 제가 도움을 줄 수 있다면, 나는 주위를 놀라지 않을 것입니다 :
(a) 따옴표 안에 인용 부호 안에 인용 부호, 그리고
(b) sys 테이블 및 / 또는 부적절한 자체 참조 연결 서버 설정 (예 : 모든 경우 DBA에게 내 사례를 호소해야합니다. :)

그러나이 경우에는 sp_help_job 에서 이미 INSERT ... EXEC 구문을 사용할 수 없으므로 사용할 수 없습니다. ( "INSERT EXEC 문을 중첩 할 수 없습니다.")


다음은 문제를 해결하는 다양한 방법을 설명하는 꽤 좋은 문서에 대한 링크입니다. 기존 스토어드 프로 시저를 수정할 수 없으므로 많은 메소드를 사용할 수 없습니다.

저장 프로 시저 간의 데이터 공유 방법

Gulzar의 답변은 작동합니다 (위의 링크에 설명되어 있음). 그러나 작성하는 번거 로움이 있습니다. @tablevar (col1, ...) 문에 80 개의 열 이름을 모두 지정해야합니다. 그리고 앞으로 스키마에 열이 추가되거나 출력이 변경되면 코드에서 업데이트해야합니다. 그렇지 않으면 오류가 발생합니다.


이를 위해 먼저 아래와 같이 #test_table 을 만듭니다.

create table #test_table(
    col1 int,
    col2 int,
   .
   .
   .
    col80 int
)

이제 프로 시저를 실행하고 #test_table 에 값을 #test_table .

insert into #test_table
EXEC MyStoredProc 'param1', 'param2'

이제 #test_table 에서 값을 가져옵니다.

select col1,col2....,col80 from #test_table

이 작업을 한 번만 수행하면 가장 쉬운 방법입니다.

가져 오기 및 내보내기 마법사에서 Excel로 내보내기를 수행 한 다음이 Excel을 테이블로 가져옵니다.


(가정 SQL 서버)

T-SQL에서 저장 프로 시저의 결과를 처리하는 유일한 방법은 INSERT INTO ... EXEC 구문을 사용하는 것입니다. 그러면 임시 테이블이나 테이블 변수에 삽입 할 수있는 옵션이 생기고 거기에서 필요한 데이터를 선택할 수 있습니다.


질문에서 언급했듯이 저장 프로 시저를 실행하기 전에 80 열 임시 테이블을 정의하기가 어렵습니다.

따라서이 문제를 해결하는 다른 방법은 저장 프로 시저 결과 집합을 기반으로 테이블을 채우는 것입니다.

SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;'
                                   ,'EXEC MyStoredProc')

오류가 발생하면 다음 쿼리를 실행하여 임시 분산 쿼리를 사용하도록 설정해야합니다.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

두 매개 변수로 sp_configure 를 실행하여 구성 옵션을 변경하거나 RECONFIGURE 문을 실행하려면 ALTER SETTINGS 서버 수준 권한이 부여되어야합니다

이제 생성 된 테이블에서 특정 열을 선택할 수 있습니다.

SELECT col1, col2
FROM #temp




stored-procedures