sql-server - 프로그래밍 - sql 프로 시저 예제




저장 프로 시저의 결과 집합에서 열 선택 (10)

(가정 SQL 서버)

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

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'키워드 근처의 구문이 잘못되었습니다.


SQL Server의 경우이 기능이 제대로 작동합니다.

임시 테이블 (또는 영구 테이블, 정말로 중요하지 않음)을 만들고 저장 프로 시저에 대한 문에 삽입합니다. SP의 결과 집합이 테이블의 열과 일치해야합니다. 그렇지 않으면 오류가 발생합니다.

다음은 그 예입니다.

DECLARE @temp TABLE (firstname NVARCHAR(30), lastname nvarchar(50));

INSERT INTO @temp EXEC dbo.GetPersonName @param1,@param2;
-- assumption is that dbo.GetPersonName returns a table with firstname / lastname columns

SELECT * FROM @temp;

그게 다야!


데이터의 수동 유효성 검사를 위해이 작업을 수행하는 경우 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


빠른 해킹은 새로운 매개 변수 '@Column_Name' 을 추가하고 호출 함수가 검색 할 열 이름을 정의하게하는 것입니다. sproc의 반환 부분에서 if / else 문을 사용하고 지정된 열만 반환하거나 비어있는 경우 모두 반환하십시오.

CREATE PROCEDURE [dbo].[MySproc]
        @Column_Name AS VARCHAR(50)
AS
BEGIN
    IF (@Column_Name = 'ColumnName1')
        BEGIN
            SELECT @ColumnItem1 as 'ColumnName1'
        END
    ELSE
        BEGIN
            SELECT @ColumnItem1 as 'ColumnName1', @ColumnItem2 as 'ColumnName2', @ColumnItem3 as 'ColumnName3'
        END
END

이 시도

use mydatabase
create procedure sp_onetwothree as
select 1 as '1', 2 as '2', 3 as '3'
go
SELECT a.[1], a.[2]
FROM OPENROWSET('SQLOLEDB','myserver';'sa';'mysapass',
    'exec mydatabase.dbo.sp_onetwothree') AS a
GO

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

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


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

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


이를 위해 먼저 아래와 같이 #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

질문에서 언급했듯이 저장 프로 시저를 실행하기 전에 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

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

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

SELECT col1, col2 FROM @tablevar




stored-procedures