SQL Server: SELECT FROM stored procedure

Ho una stored procedure che restituisce righe:

CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END 

La mia procedura attuale è un po ‘più complicata, motivo per cui è necessario uno sproc.

È ansible selezionare l’output chiamando questa procedura?

Qualcosa di simile a:

 SELECT * FROM (EXEC MyProc) AS TEMP 

Devo utilizzare SELECT TOP X , ROW_NUMBER e una clausola WHERE aggiuntiva per ROW_NUMBER ai miei dati e non voglio davvero passare questi valori come parametri.

È ansible utilizzare una funzione definita dall’utente o una vista anziché una procedura.

Una procedura può restituire più set di risultati, ciascuno con il proprio schema. Non è adatto per l’utilizzo in un’istruzione SELECT .

Dovresti dare un’occhiata a questo eccellente articolo di Erland Sommarskog:

  • Come condividere i dati tra stored procedure

In pratica elenca tutte le opzioni disponibili per il tuo scenario.

Puoi

  1. creare una variabile di tabella per contenere il set di risultati dal proc memorizzato e quindi
  2. inserire l’output del proc memorizzato nella variabile di tabella e quindi
  3. usa la variabile della tabella esattamente come faresti con qualsiasi altra tabella …

… sql ….

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

O vuoi una funzione con valori di tabella o inserisci il tuo EXEC in una tabella temporanea:

 INSERT INTO #tab EXEC MyProc 

Devi leggere su OPENROWSET e OPENQUERY

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

Non è necessario utilizzare una tabella temporanea.

Questa è la mia soluzione

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

È necessario dichiarare un tipo di tabella che contiene lo stesso numero di colonne restituite dalla procedura di archiviazione. I tipi di dati delle colonne nel tipo di tabella e le colonne restituite dalle procedure dovrebbero essere uguali

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

Quindi è necessario inserire il risultato della stored procedure nel tipo di tabella appena definito

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

Alla fine basta selezionare dal tuo tipo di tabella

 Select * from @MyTableType 

È ansible copiare l’output dalla tabella sp a temporaty.

 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 

usa OPENQUERY e prima che Execute set ‘SET FMTONLY OFF; SET NOCOUNT ON; ‘

Prova questo codice di esempio:

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

Puoi imbrogliare un po ‘con OPENROWSET:

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

Questo, comunque, funzionerebbe sempre l’intero SP, naturalmente.

Prova a convertire la tua procedura in una funzione Inline che restituisce una tabella come segue:

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

E poi puoi chiamarlo come

 SELECT * FROM MyProc() 

Hai anche la possibilità di passare parametri alla funzione come segue:

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

E chiamalo

 SELECT * FROM FuncName ( @para1 , @para2 ) 

Se “ACCESSO DATI” falso,

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

dopo,

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

Funziona.

Per semplicità e per renderlo eseguibile, ho usato un sistema StoredProcedure “sp_readerrorlog” per ottenere i dati:

 -----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 

Sembra che potresti dover semplicemente usare una vista . Una vista consente di rappresentare una query come una tabella in modo che possa essere interrogata la vista.

Ad esempio, se il tuo server si chiama SERVERX, ecco come l’ho fatto …

 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); 

Per verificare che funzionasse, ho commentato la riga di comando EXEC() e l’ho sostituita con SELECT @CMD per rivedere il comando prima di provare ad eseguirlo! Questo per assicurarsi che tutto il numero corretto di virgolette singole fosse nel posto giusto. 🙂

Spero che aiuti qualcuno.