SQL Server - WYBIERZ Z procedury składowanej


334

Mam procedurę składowaną, która zwraca wiersze:

CREATE PROCEDURE MyProc
AS
BEGIN
    SELECT * FROM MyTable
END

Moja faktyczna procedura jest nieco bardziej skomplikowana i dlatego potrzebna jest sproc.

Czy można wybrać wyjście, wywołując tę ​​procedurę?

Coś jak:

SELECT * FROM (EXEC MyProc) AS TEMP

Potrzebuję użyć SELECT TOP X, ROW_NUMBERi dodatkowej WHEREklauzuli do stronicowania moich danych, i tak naprawdę nie chcę przekazywać tych wartości jako parametrów.


Nie jestem pewien, co zamierzasz tutaj zrobić, ponieważ po wykonaniu procedury odzyskujesz wiersze. Czy chcesz wykonać procedurę w instrukcji SELECT, aby powiązać ją z obiektem, który można przewijać?
Raj More

1
Czy istnieje konkretny powód, dla którego nie chcesz przekazywać wartości jako parametrów? Aby to zrobić, tak jak sugerujesz, jest nieco nieefektywne - wybrałbyś więcej danych niż potrzebujesz, a następnie nie wykorzystałbyś wszystkich.
Mark Bell

Odpowiedzi:


149

Zamiast procedury można użyć funkcji zdefiniowanej przez użytkownika lub widoku .

Procedura może zwrócić wiele zestawów wyników, każdy z własnym schematem. Nie nadaje się do użycia w SELECToświadczeniu.


8
Ponadto, jeśli po konwersji do UDF okaże się, że potrzebujesz semantyki procedury składowanej, zawsze możesz zawinąć UDF z procedurą.
Joel Coehoorn

co jeśli musimy wysłać parametry do wielu procedur przechowywanych i połączyć je w jedną dużą procedurę przechowywaną? Może przeglądać, pobierać parametry, podobnie jak procedury składowane
mrN

3
@mrN Widoki nie przyjmują parametrów, ale UDF.
Mehrdad Afshari,

3
Witaj, naprawdę muszę to zrobić bez konwersji SP do widoku lub funkcji, czy to możliwe?
Luis Becerril,

2
Chociaż twoja odpowiedź jest prawdziwym stwierdzeniem, nie odpowiada na pytanie… „WYBIERZ Z procedury składowanej” Co na pewno nie jest idealne, ale takie jest ... Odpowiedź Aamira jest poprawna. Albo to, albo pytanie musi zostać zmienione ... co wydaje mi się trochę śmieszne.
Urasquirrel

202

Możesz

  1. utwórz zmienną tabelową, aby przechowywać zestaw wyników z zapisanego proc, a następnie
  2. wstaw wyjście zapisanego proc do zmiennej tabeli, a następnie
  3. użyj zmiennej tabeli dokładnie tak, jak każdej innej tabeli ...

... sql ....

Declare @T Table ([column definitions here])
Insert @T Exec storedProcname params 
Select * from @T Where ...

34
Problem z INSERT #Tlub INSERT @Tjest to, że INSERT EXECoświadczenie nie mogą być zagnieżdżone. Jeśli procedura przechowywana ma już INSERT EXECw sobie, to nie zadziała.
MOHCTP

2
To prawdopodobnie najbardziej przenośne rozwiązanie, ponieważ jest najbliżej podstawowego SQL. Pomaga także zachować silne definicje typów kolumn. Powinny mieć więcej głosów pozytywnych niż te powyżej.

Te zmienne tabela wygląda bardziej przydatna, niż tabel tymczasowych w zakresie sp ponownej kompilacji. Zgadzam się, ta odpowiedź powinna mieć więcej pozytywnych opinii.
resnyanskiy

76

Możesz albo użyć funkcji Valued Table, albo wstawić EXEC do tabeli tymczasowej:

INSERT INTO #tab EXEC MyProc

32
Problem z INSERT #Tlub INSERT @Tjest to, że INSERT EXECoświadczenie nie mogą być zagnieżdżone. Jeśli procedura przechowywana ma już INSERT EXECw sobie, to nie zadziała.
MOHCTP

46

Musisz przeczytać o OPENROWSET i OPENQUERY

SELECT  * 
INTO    #tmp FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')

4
Jak uzyskać SWOJĄ NAZWĘ dynamicznie? Nie możesz oczekiwać, że będziesz musiał zawsze wiedzieć. Czy ta przerwa nie będzie co drugi wtorek? Więc jeśli mam 100 serwerów o różnych nazwach ...
Urasquirrel,

co również, jeśli moja baza danych nie jest skonfigurowana, aby to umożliwić?
Urasquirrel

4
Spróbuj @@ nazwa serwera, aby uzyskać ją dynamicznie
Siddhartha Gandhi

44

Musisz zadeklarować typ tabeli zawierający tę samą liczbę kolumn, którą zwraca procedura sklepu. Typy danych kolumn w typie tabeli i kolumn zwracanych przez procedury powinny być takie same

declare @MyTableType as table
(
FIRSTCOLUMN int
,.....
)  

Następnie musisz wstawić wynik procedury składowanej do właśnie zdefiniowanego typu tabeli

Insert into @MyTableType 
EXEC [dbo].[MyStoredProcedure]

Na koniec wybierz po prostu swój typ stołu

Select * from @MyTableType

Jest to dla mnie najlepsze rozwiązanie, ponieważ nie trzeba podawać nazwy serwera, parametrów połączenia ani konfigurować żadnych połączonych serwerów, aby działało - to rzeczy, których nie chcę robić tylko po to, aby uzyskać niektóre dane z powrotem. Dziękuję Ci! Świetna odpowiedź!
Matt

Nicea odpowiedź ღ❤ ೋ ღ❤ღ ೋ❤ ღ
Nahid

Gdy procedura przechowywana jest zbyt trudna - ta metoda nie działa, na przykład, gdy procedura przechowywana korzysta z dwóch tabel tymczasowych.
nick_n_a

34

Nie jest konieczne stosowanie tabeli tymczasowej.

To jest moje rozwiązanie

SELECT  *  FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
WHERE somefield = anyvalue

2
Wymaga to dodania do siebie serwera jako serwera połączonego, ale działa jak urok! dzięki!
vaheeds

Kilka świetnych uwag na ten temat: stackoverflow.com/questions/2374741/...
Keith Adler

1
Hmm ... Pojawia się błąd „Błąd 7411: Serwer„ TwojaSerwerNazwa ”nie jest skonfigurowany dla DANYCH DOSTĘPU.” Co muszę zmienić?
Matt

Czy dodałeś swój serwer jako serwer połączony? YourServerName to nazwa twojego serwera. Musisz zmienić YourServerName na swoją prawdziwą nazwę serwera.
DavideDM,

@Matt:sp_serveroption 'MYSERVER', 'DATA ACCESS', TRUE;
alexkovelsky,

23

Możesz skopiować dane wyjściowe ze sp do tabeli tymczasowej.

CREATE TABLE #GetVersionValues
(
    [Index] int,
    [Name]  sysname,
    Internal_value  int,
    Character_Value sysname
)
INSERT #GetVersionValues EXEC master.dbo.xp_msver 'WindowsVersion'
SELECT * FROM #GetVersionValues
drop TABLE #GetVersionValues

7

użyj OPENQUERY i przed Wykonaj ustaw „SET FMTONLY OFF; USTAW NOCOUNT WŁ; ”

Wypróbuj ten przykładowy kod:

SELECT top(1)*
FROM
OPENQUERY( [Server], 'SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE  [database].[dbo].[storedprocedure]  value,value ')

6

Spróbuj przekonwertować procedurę na funkcję wbudowaną, która zwraca tabelę w następujący sposób:

CREATE FUNCTION MyProc()
RETURNS TABLE AS
RETURN (SELECT * FROM MyTable)

A potem możesz to nazwać

SELECT * FROM MyProc()

Możesz również przekazać parametry do funkcji w następujący sposób:

CREATE FUNCTION FuncName (@para1 para1_type, @para2 para2_type , ... ) 

I nazwij to

SELECT * FROM FuncName ( @para1 , @para2 )

6

Jeśli „DATA ACCESS” jest fałszywe,

EXEC sp_serveroption 'SQLSERVERNAME', 'DATA ACCESS', TRUE

po,

SELECT  *  FROM OPENQUERY(SQLSERVERNAME, 'EXEC DBNAME..MyProc @parameters')

to działa.


5

Możesz oszukać trochę za pomocą OPENROWSET:

SELECT ...fieldlist...
FROM OPENROWSET('SQLNCLI', 'connection string', 'name of sp')
WHERE ...

Oczywiście nadal działałby cały SP.


4

Ze względu na prostotę i ponowne uruchomienie, użyłem systemu StoredProcedure „sp_readerrorlog”, aby uzyskać dane:

-----USING Table Variable
DECLARE @tblVar TABLE (
   LogDate DATETIME,
   ProcessInfo NVARCHAR(MAX),
   [Text] NVARCHAR(MAX)
)
INSERT INTO @tblVar Exec sp_readerrorlog
SELECT LogDate as DateOccured, ProcessInfo as pInfo, [Text] as Message FROM @tblVar



-----(OR): Using Temp Table
IF OBJECT_ID('tempdb..#temp') IS NOT NULL  DROP TABLE #temp;
CREATE TABLE #temp (
   LogDate DATETIME,
   ProcessInfo NVARCHAR(55),
   Text NVARCHAR(MAX)
)
INSERT INTO #temp EXEC sp_readerrorlog
SELECT * FROM #temp

1

Wygląda na to, że możesz potrzebować użyć widoku . Widok pozwala na reprezentację zapytania jako tabeli, dzięki czemu można przeglądać widok.


1

Jeśli na przykład twój serwer nazywa się SERVERX, tak to zrobiłem ...

EXEC sp_serveroption 'SERVERX', 'DATA ACCESS', TRUE;
DECLARE @CMD VARCHAR(1000);
DECLARE @StudentID CHAR(10);
SET @StudentID = 'STUDENT01';
SET @CMD = 'SELECT * FROM OPENQUERY([SERVERX], ''SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE MYDATABASE.dbo.MYSTOREDPROC ' + @StudentID + ''') WHERE SOMEFIELD = SOMEVALUE';
EXEC (@CMD);

Aby sprawdzić, czy to działa, skomentowałem EXEC()wiersz polecenia i zastąpiłem go, SELECT @CMDaby przejrzeć polecenie przed próbą jego wykonania! Miało to na celu upewnienie się, że cała poprawna liczba pojedynczych cudzysłowów znajduje się we właściwym miejscu. :-)

Mam nadzieję, że to komuś pomoże.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.