SQL Server - SELECT FROM stored procedure

Question

I have a stored procedure that returns rows   CREATE PROCEDURE MyProc AS BEGIN     SELECT   FROM MyTable END   My actual procedure is a little more complicated  which is why a sproc is necessary   Is it possible to select the output by calling this procedure   Something like   SELECT   FROM  EXEC MyProc  AS TEMP   I need to use SELECT TOP X  ROW NUMBER  and an additional WHERE clause to page my data  and I don t really want to pass these values as parameters

User · Answer

You can copy output from sp to temporaty table   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

User · Answer

It sounds like you might just need to use a view   A view allows a query to be represented as a table so it  the view  can be queried

User · Answer

If your server is called SERVERX for example  this is how I did it     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     To check this worked  I commented out the EXEC   command line and replaced it with SELECT  CMD to review the command before trying to execute it  That was to make sure all the correct number of single-quotes were in the right place    -   I hope that helps someone

User · Answer

You can cheat a little with OPENROWSET    SELECT    fieldlist    FROM OPENROWSET  SQLNCLI    connection string    name of sp   WHERE       This would still run the entire SP every time  of course

User · Answer

For the sake of simplicity and to make it re-runnable  I have used a system StoredProcedure  sp readerrorlog  to get data   -----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

User · Answer

You can use a User-defined function or a view instead of a procedure   A procedure can return multiple result sets  each with its own schema  It s not suitable for using in a SELECT statement

User · Answer

use OPENQUERY and befor Execute set  SET FMTONLY OFF  SET NOCOUNT ON    Try this sample code   SELECT top 1   FROM OPENQUERY   Server    SET FMTONLY OFF  SET NOCOUNT ON  EXECUTE   database   dbo   storedprocedure   value value

User · Answer

You must read about OPENROWSET and OPENQUERY  SELECT     INTO     tmp FROM     OPENQUERY YOURSERVERNAME   EXEC MyProc  parameters

User · Answer

You either want a Table-Valued function or insert your EXEC into a temporary table   INSERT INTO  tab EXEC MyProc

User · Answer

Try converting your procedure in to an Inline Function which returns a table as follows   CREATE FUNCTION MyProc   RETURNS TABLE AS RETURN  SELECT   FROM MyTable    And then you can call it as   SELECT   FROM MyProc     You also have the option of passing parameters to the function as follows   CREATE FUNCTION FuncName   para1 para1 type   para2 para2 type            And call it  SELECT   FROM FuncName    para1    para2

User · Answer

You can    create a table variable to hold the result set from the stored proc and then   insert the output of the     stored proc into the table variable      and then   use the table variable         exactly as you would any other         table          sql       Declare  T Table   column definitions here   Insert  T Exec storedProcname params  Select   from  T Where

User · Answer

You need to declare a table type which contains the same number of columns your store procedure is returning  Data types of the columns in the table type and the columns returned by the procedures should be same  declare  MyTableType as table   FIRSTCOLUMN int              Then you need to insert the result of your stored procedure in your table type you just defined  Insert into  MyTableType  EXEC  dbo   MyStoredProcedure    In the end just select from your table type  Select   from  MyTableType

User · Answer

If  DATA ACCESS  false   EXEC sp serveroption  SQLSERVERNAME    DATA ACCESS   TRUE   after   SELECT     FROM OPENQUERY SQLSERVERNAME   EXEC DBNAME  MyProc  parameters     it works

User · Answer

It is not necessary use a temporary table    This is my solution  SELECT     FROM     OPENQUERY YOURSERVERNAME   EXEC MyProc  parameters   WHERE somefield   anyvalue

[sql] SQL Server - SELECT FROM stored procedure

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to stored-procedures