Select columns from result set of stored procedure

Question

I have a stored procedure that returns 80 columns  and 300 rows   I want to write a select that gets 2 of those columns   Something like   SELECT col1  col2 FROM EXEC MyStoredProc  param1    param2    When I used the above syntax I get the error        Invalid Column Name     I know the easiest solution would be to change the stored procedure  but I didn t write it  and I can t change it     Is there any way to do what I want    I could make a temp table to put the results in  but because there are 80 columns so I would need to make an 80 column temp table just to get 2 columns   I wanted to avoid tracking down all the columns that are returned  I tried using WITH SprocResults AS      as suggested by Mark  but I got 2 errors      Incorrect syntax near the keyword  EXEC  Incorrect syntax near       I tried declaring a table variable and I got the following error      Insert Error  Column name or number of supplied values does not match table definition  If I try  SELECT   FROM EXEC MyStoredProc  param1    param2  I get the error       Incorrect syntax near the keyword  exec

User · Answer

For anyone who has SQL 2012 or later  I was able to accomplish this with stored procedures that aren t dynamic and have the same columns output each time   The general idea is I build the dynamic query to create  insert into  select from  and drop the temp table  and execute this after it s all generated  I dynamically generate the temp table by first retrieving column names and types from the stored procedure   Note  there are much better  more universal solutions that will work with fewer lines of code if you re willing able to update the SP or change configuration and use OPENROWSET  Use the below if you have no other way   DECLARE  spName VARCHAR MAX     MyStoredProc  DECLARE  tempTableName VARCHAR MAX      tempTable   -- might need to update this if your param value is a string and you need to escape quotes DECLARE  insertCommand VARCHAR MAX     INSERT INTO      tempTableName     EXEC MyStoredProc  param value   DECLARE  createTableCommand VARCHAR MAX   -- update this to select the columns you want DECLARE  selectCommand VARCHAR MAX     SELECT col1  col2 FROM      tempTableName  DECLARE  dropCommand VARCHAR MAX     DROP TABLE      tempTableName  -- Generate command to create temp table SELECT  createTableCommand    CREATE TABLE      tempTableName              STUFF                             SELECT        CONCAT      name            system type name              FROM sys dm exec describe first result set for object                             OBJECT ID  spName                  NULL                           FOR XML PATH                        1          1                          EXEC   createTableCommand         insertCommand          selectCommand          dropCommand

User · Answer

As it s been mentioned in the question  it s hard to define the 80 column temp table before executing the stored procedure   So the other way around this is to populate the table based on the stored procedure result set   SELECT   INTO  temp FROM OPENROWSET  SQLNCLI    Server localhost Trusted Connection yes                                        EXEC MyStoredProc     If you are getting any error  you need to enable ad hoc distributed queries by executing following query   sp configure  Show Advanced Options   1 GO RECONFIGURE GO sp configure  Ad Hoc Distributed Queries   1 GO RECONFIGURE GO   To execute sp configure with both parameters to change a configuration option or to run the RECONFIGURE statement  you must be granted the ALTER SETTINGS server-level permission  Now you can select your specific columns from the generated table  SELECT col1  col2 FROM  temp

User · Answer

A quick hack would be to add a new parameter   Column Name  and have the calling function define the column name to be retrieved  In the return part of your sproc  you would have if else statements and return only the specified column  or if empty - return all   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

User · Answer

Can you split up the query  Insert the stored proc results into a table variable or a temp table  Then  select the 2 columns from the table variable   Declare  tablevar table col1 col1Type    insert into  tablevar col1     exec MyStoredProc  param1    param2   SELECT col1  col2 FROM  tablevar

User · Answer

Easiest way to do if you only need to this once   Export to excel in Import and Export wizard and then import this excel into a table

User · Answer

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

User · Answer

Can you split up the query  Insert the stored proc results into a table variable or a temp table  Then  select the 2 columns from the table variable   Declare  tablevar table col1 col1Type    insert into  tablevar col1     exec MyStoredProc  param1    param2   SELECT col1  col2 FROM  tablevar

User · Answer

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

User · Answer

Here s a link to a pretty good document explaining all the different ways to solve your problem  although a lot of them can t be used since you can t modify the existing stored procedure    How to Share Data Between Stored Procedures  Gulzar s answer will work  it is documented in the link above  but it s going to be a hassle to write  you ll need to specify all 80 column names in your  tablevar col1      statement  And in the future if a column is added to the schema or the output is changed it will need to be updated in your code or it will error out

User · Answer

To achieve this  first you create a  test table like below   create table  test table      col1 int      col2 int                     col80 int     Now execute procedure and put value in  test table   insert into  test table EXEC MyStoredProc  param1    param2    Now you fetch the value from  test table   select col1 col2     col80 from  test table

User · Answer

I d cut and paste the original SP and delete all columns except the 2 you want   Or   I d bring the result set back  map it to a proper business object  then LINQ out the two columns

User · Answer

For anyone who has SQL 2012 or later  I was able to accomplish this with stored procedures that aren t dynamic and have the same columns output each time   The general idea is I build the dynamic query to create  insert into  select from  and drop the temp table  and execute this after it s all generated  I dynamically generate the temp table by first retrieving column names and types from the stored procedure   Note  there are much better  more universal solutions that will work with fewer lines of code if you re willing able to update the SP or change configuration and use OPENROWSET  Use the below if you have no other way   DECLARE  spName VARCHAR MAX     MyStoredProc  DECLARE  tempTableName VARCHAR MAX      tempTable   -- might need to update this if your param value is a string and you need to escape quotes DECLARE  insertCommand VARCHAR MAX     INSERT INTO      tempTableName     EXEC MyStoredProc  param value   DECLARE  createTableCommand VARCHAR MAX   -- update this to select the columns you want DECLARE  selectCommand VARCHAR MAX     SELECT col1  col2 FROM      tempTableName  DECLARE  dropCommand VARCHAR MAX     DROP TABLE      tempTableName  -- Generate command to create temp table SELECT  createTableCommand    CREATE TABLE      tempTableName              STUFF                             SELECT        CONCAT      name            system type name              FROM sys dm exec describe first result set for object                             OBJECT ID  spName                  NULL                           FOR XML PATH                        1          1                          EXEC   createTableCommand         insertCommand          selectCommand          dropCommand

User · Answer

I know executing from sp and insert into temp table or table variable would be an option but I don t think that s your requirement  As per your requirement this below query statement should work   Declare  sql nvarchar max  Set  sql  SELECT   col1  col2 FROM OPENROWSET   SQLNCLI      Server  local  uid test pwd test           EXEC MyStoredProc     param1          param2          Exec  sql    if you have trusted connection then use this below query statement    Declare  sql nvarchar max  Set  sql  SELECT   col1  col2 FROM OPENROWSET   SQLNCLI      Server  local  Trusted Connection yes            EXEC MyStoredProc     param1          param2          Exec  sql    if you are getting error to run the above statement then just run this statement below   sp configure  Show Advanced Options   1 GO RECONFIGURE GO sp configure  Ad Hoc Distributed Queries   1 GO RECONFIGURE GO   I hope this will help someone who will have faced this kind of similar problem  If someone would to try with temp table or table variable that should be like this below but in this scenario you should to know how many columns your sp is returning then you should create that much columns in temp table or table variable   --for table variable  Declare  t table col1 col1Type  col2 col2Type  insert into  t exec MyStoredProc  param1    param2  SELECT col1  col2 FROM  t  --for temp table create table  t col1 col1Type  col2 col2Type  insert into  t exec MyStoredProc  param1    param2  SELECT col1  col2 FROM  t

User · Answer

This works for me    i e  I only need 2 columns of the 30  returned by sp help job  SELECT name  current execution status  FROM OPENQUERY  MYSERVER      EXEC msdb dbo sp help job  job name     My Job     job aspect     JOB         Before this would work  I needed to run this  sp serveroption  MYSERVER    DATA ACCESS   TRUE       to update the sys servers table    i e  Using a self-reference within OPENQUERY seems to be disabled by default   For my simple requirement  I ran into none of the problems described in the OPENQUERY section of Lance s excellent link  Rossini  if you need to dynamically set those input parameters  then use of OPENQUERY becomes a little more fiddly  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        param1       job aspect   N     param2         -- 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    I m not sure of the differences  if any  between using sp serveroption to update the existing sys servers self-reference directly  vs  using sp addlinkedserver  as described in Lance s link  to create a duplicate alias  Note 1  I prefer OPENQUERY over OPENROWSET  given that OPENQUERY does not require the connection-string definition within the proc  Note 2  Having said all this  normally I would just use INSERT     EXEC         Yes  it s 10 mins extra typing  but if I can help it  I prefer not to jigger around with   a  quotes within quotes within quotes  and  b  sys tables  and or sneaky self-referencing Linked Server setups   i e  for these  I need to plead my case to our all-powerful DBAs     However in this instance  I couldn t use a INSERT     EXEC construct  as sp help job is already using one    quot An INSERT EXEC statement cannot be nested  quot

User · Answer

It might be helpful to know why this is so difficult   A stored procedure may only return text  print  text    or may return multiple tables  or may return no tables at all   So something like SELECT   FROM  exec sp tables  Table1  will not work

User · Answer

Can you split up the query  Insert the stored proc results into a table variable or a temp table  Then  select the 2 columns from the table variable   Declare  tablevar table col1 col1Type    insert into  tablevar col1     exec MyStoredProc  param1    param2   SELECT col1  col2 FROM  tablevar

User · Answer

If you are able to modify your stored procedure  you can easily put the required columns definitions as a parameter and use an auto-created temporary table   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   In this case you don t need to create a temp table manually - it is created automatically  Hope this helps

User · Answer

Can you split up the query  Insert the stored proc results into a table variable or a temp table  Then  select the 2 columns from the table variable   Declare  tablevar table col1 col1Type    insert into  tablevar col1     exec MyStoredProc  param1    param2   SELECT col1  col2 FROM  tablevar

User · Answer

CREATE TABLE  Result     ID int   Name varchar 500   Revenue money   INSERT  Result EXEC RevenueByAdvertiser  1 1 10    2 1 10  SELECT   FROM  Result ORDER BY Name DROP TABLE  Result   Source  http   stevesmithblog com blog select-from-a-stored-procedure

User · Answer

If you are able to modify your stored procedure  you can easily put the required columns definitions as a parameter and use an auto-created temporary table   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   In this case you don t need to create a temp table manually - it is created automatically  Hope this helps

User · Answer

Here s a link to a pretty good document explaining all the different ways to solve your problem  although a lot of them can t be used since you can t modify the existing stored procedure    How to Share Data Between Stored Procedures  Gulzar s answer will work  it is documented in the link above  but it s going to be a hassle to write  you ll need to specify all 80 column names in your  tablevar col1      statement  And in the future if a column is added to the schema or the output is changed it will need to be updated in your code or it will error out

User · Answer

If you re doing this for manual validation of the data  you can do this with LINQPad   Create a connection to the database in LinqPad then create C  statements similar to the following   DataTable table   MyStoredProc  param1  param2  Tables 0    from row in table AsEnumerable    select new      Col1   row Field lt string gt   col1      Col2   row Field lt string gt   col2        Dump      Reference http   www global-webnet net blogengine post 2008 09 10 LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet aspx

User · Answer

Assuming SQL Server   The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO     EXEC syntax   That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need

User · Answer

I know executing from sp and insert into temp table or table variable would be an option but I don t think that s your requirement  As per your requirement this below query statement should work   Declare  sql nvarchar max  Set  sql  SELECT   col1  col2 FROM OPENROWSET   SQLNCLI      Server  local  uid test pwd test           EXEC MyStoredProc     param1          param2          Exec  sql    if you have trusted connection then use this below query statement    Declare  sql nvarchar max  Set  sql  SELECT   col1  col2 FROM OPENROWSET   SQLNCLI      Server  local  Trusted Connection yes            EXEC MyStoredProc     param1          param2          Exec  sql    if you are getting error to run the above statement then just run this statement below   sp configure  Show Advanced Options   1 GO RECONFIGURE GO sp configure  Ad Hoc Distributed Queries   1 GO RECONFIGURE GO   I hope this will help someone who will have faced this kind of similar problem  If someone would to try with temp table or table variable that should be like this below but in this scenario you should to know how many columns your sp is returning then you should create that much columns in temp table or table variable   --for table variable  Declare  t table col1 col1Type  col2 col2Type  insert into  t exec MyStoredProc  param1    param2  SELECT col1  col2 FROM  t  --for temp table create table  t col1 col1Type  col2 col2Type  insert into  t exec MyStoredProc  param1    param2  SELECT col1  col2 FROM  t

User · Answer

Create a dynamic view and get result from it         CREATE PROCEDURE dbo usp userwise columns value        userid BIGINT   AS  BEGIN         DECLARE  maincmd NVARCHAR max           DECLARE  columnlist NVARCHAR max           DECLARE  columnname VARCHAR 150           DECLARE  nickname VARCHAR 50            SET  maincmd               SET  columnname               SET  columnlist               SET  nickname                DECLARE CUR COLUMNLIST CURSOR FAST FORWARD         FOR             SELECT columnname   nickname             FROM dbo v userwise columns              WHERE userid    userid          OPEN CUR COLUMNLIST         IF   ERROR  lt  gt  0             BEGIN                 ROLLBACK                 RETURN             END             FETCH NEXT FROM CUR COLUMNLIST         INTO  columnname   nickname          WHILE   FETCH STATUS   0             BEGIN                 SET  columnlist    columnlist    columnname                        FETCH NEXT FROM CUR COLUMNLIST                 INTO  columnname   nickname             END         CLOSE CUR COLUMNLIST         DEALLOCATE CUR COLUMNLIST            IF NOT EXISTS  SELECT   FROM sys views WHERE name    v userwise columns value               BEGIN                 SET  maincmd    CREATE VIEW dbo v userwise columns value AS SELECT sjoid  CONVERT BIGINT      CONVERT VARCHAR 10    userid       as userid                                    CHAR 39     nickname   CHAR 39      as nickname                                    columnlist     compcode FROM dbo SJOTran               END         ELSE             BEGIN                 SET  maincmd    ALTER VIEW dbo v userwise columns value AS SELECT sjoid  CONVERT BIGINT      CONVERT VARCHAR 10    userid       as userid                                    CHAR 39     nickname   CHAR 39      as nickname                                    columnlist     compcode FROM dbo SJOTran               END          --PRINT  maincmd         EXECUTE sp executesql  maincmd END  ----------------------------------------------- SELECT   FROM dbo v userwise columns value

User · Answer

Assuming SQL Server   The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO     EXEC syntax   That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need

User · Answer

I d cut and paste the original SP and delete all columns except the 2 you want   Or   I d bring the result set back  map it to a proper business object  then LINQ out the two columns

User · Answer

For SQL Server  I find that this works fine   Create a temp table  or permanent table  doesn t really matter   and do a insert into statement against the stored procedure   The result set of the SP should match the columns in your table  otherwise you ll get an error   Here s an example   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    That s it

User · Answer

Easiest way to do if you only need to this once   Export to excel in Import and Export wizard and then import this excel into a table

User · Answer

Create a dynamic view and get result from it         CREATE PROCEDURE dbo usp userwise columns value        userid BIGINT   AS  BEGIN         DECLARE  maincmd NVARCHAR max           DECLARE  columnlist NVARCHAR max           DECLARE  columnname VARCHAR 150           DECLARE  nickname VARCHAR 50            SET  maincmd               SET  columnname               SET  columnlist               SET  nickname                DECLARE CUR COLUMNLIST CURSOR FAST FORWARD         FOR             SELECT columnname   nickname             FROM dbo v userwise columns              WHERE userid    userid          OPEN CUR COLUMNLIST         IF   ERROR  lt  gt  0             BEGIN                 ROLLBACK                 RETURN             END             FETCH NEXT FROM CUR COLUMNLIST         INTO  columnname   nickname          WHILE   FETCH STATUS   0             BEGIN                 SET  columnlist    columnlist    columnname                        FETCH NEXT FROM CUR COLUMNLIST                 INTO  columnname   nickname             END         CLOSE CUR COLUMNLIST         DEALLOCATE CUR COLUMNLIST            IF NOT EXISTS  SELECT   FROM sys views WHERE name    v userwise columns value               BEGIN                 SET  maincmd    CREATE VIEW dbo v userwise columns value AS SELECT sjoid  CONVERT BIGINT      CONVERT VARCHAR 10    userid       as userid                                    CHAR 39     nickname   CHAR 39      as nickname                                    columnlist     compcode FROM dbo SJOTran               END         ELSE             BEGIN                 SET  maincmd    ALTER VIEW dbo v userwise columns value AS SELECT sjoid  CONVERT BIGINT      CONVERT VARCHAR 10    userid       as userid                                    CHAR 39     nickname   CHAR 39      as nickname                                    columnlist     compcode FROM dbo SJOTran               END          --PRINT  maincmd         EXECUTE sp executesql  maincmd END  ----------------------------------------------- SELECT   FROM dbo v userwise columns value

User · Answer

It might be helpful to know why this is so difficult   A stored procedure may only return text  print  text    or may return multiple tables  or may return no tables at all   So something like SELECT   FROM  exec sp tables  Table1  will not work

User · Answer

This works for me    i e  I only need 2 columns of the 30  returned by sp help job  SELECT name  current execution status  FROM OPENQUERY  MYSERVER      EXEC msdb dbo sp help job  job name     My Job     job aspect     JOB         Before this would work  I needed to run this  sp serveroption  MYSERVER    DATA ACCESS   TRUE       to update the sys servers table    i e  Using a self-reference within OPENQUERY seems to be disabled by default   For my simple requirement  I ran into none of the problems described in the OPENQUERY section of Lance s excellent link  Rossini  if you need to dynamically set those input parameters  then use of OPENQUERY becomes a little more fiddly  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        param1       job aspect   N     param2         -- 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    I m not sure of the differences  if any  between using sp serveroption to update the existing sys servers self-reference directly  vs  using sp addlinkedserver  as described in Lance s link  to create a duplicate alias  Note 1  I prefer OPENQUERY over OPENROWSET  given that OPENQUERY does not require the connection-string definition within the proc  Note 2  Having said all this  normally I would just use INSERT     EXEC         Yes  it s 10 mins extra typing  but if I can help it  I prefer not to jigger around with   a  quotes within quotes within quotes  and  b  sys tables  and or sneaky self-referencing Linked Server setups   i e  for these  I need to plead my case to our all-powerful DBAs     However in this instance  I couldn t use a INSERT     EXEC construct  as sp help job is already using one    quot An INSERT EXEC statement cannot be nested  quot

User · Answer

As it s been mentioned in the question  it s hard to define the 80 column temp table before executing the stored procedure   So the other way around this is to populate the table based on the stored procedure result set   SELECT   INTO  temp FROM OPENROWSET  SQLNCLI    Server localhost Trusted Connection yes                                        EXEC MyStoredProc     If you are getting any error  you need to enable ad hoc distributed queries by executing following query   sp configure  Show Advanced Options   1 GO RECONFIGURE GO sp configure  Ad Hoc Distributed Queries   1 GO RECONFIGURE GO   To execute sp configure with both parameters to change a configuration option or to run the RECONFIGURE statement  you must be granted the ALTER SETTINGS server-level permission  Now you can select your specific columns from the generated table  SELECT col1  col2 FROM  temp

User · Answer

Here s a link to a pretty good document explaining all the different ways to solve your problem  although a lot of them can t be used since you can t modify the existing stored procedure    How to Share Data Between Stored Procedures  Gulzar s answer will work  it is documented in the link above  but it s going to be a hassle to write  you ll need to specify all 80 column names in your  tablevar col1      statement  And in the future if a column is added to the schema or the output is changed it will need to be updated in your code or it will error out

User · Answer

To achieve this  first you create a  test table like below   create table  test table      col1 int      col2 int                     col80 int     Now execute procedure and put value in  test table   insert into  test table EXEC MyStoredProc  param1    param2    Now you fetch the value from  test table   select col1 col2     col80 from  test table

User · Answer

Assuming SQL Server   The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO     EXEC syntax   That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need

User · Answer

Here s a link to a pretty good document explaining all the different ways to solve your problem  although a lot of them can t be used since you can t modify the existing stored procedure    How to Share Data Between Stored Procedures  Gulzar s answer will work  it is documented in the link above  but it s going to be a hassle to write  you ll need to specify all 80 column names in your  tablevar col1      statement  And in the future if a column is added to the schema or the output is changed it will need to be updated in your code or it will error out

User · Answer

CREATE TABLE  Result     ID int   Name varchar 500   Revenue money   INSERT  Result EXEC RevenueByAdvertiser  1 1 10    2 1 10  SELECT   FROM  Result ORDER BY Name DROP TABLE  Result   Source  http   stevesmithblog com blog select-from-a-stored-procedure

User · Answer

Assuming SQL Server   The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO     EXEC syntax   That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need

User · Answer

A quick hack would be to add a new parameter   Column Name  and have the calling function define the column name to be retrieved  In the return part of your sproc  you would have if else statements and return only the specified column  or if empty - return all   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

User · Answer

For SQL Server  I find that this works fine   Create a temp table  or permanent table  doesn t really matter   and do a insert into statement against the stored procedure   The result set of the SP should match the columns in your table  otherwise you ll get an error   Here s an example   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    That s it

User · Answer

If you re doing this for manual validation of the data  you can do this with LINQPad   Create a connection to the database in LinqPad then create C  statements similar to the following   DataTable table   MyStoredProc  param1  param2  Tables 0    from row in table AsEnumerable    select new      Col1   row Field lt string gt   col1      Col2   row Field lt string gt   col2        Dump      Reference http   www global-webnet net blogengine post 2008 09 10 LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet aspx

[sql-server] Select columns from result set of stored procedure

Examples related to sql-server

Examples related to tsql

Examples related to select

Examples related to stored-procedures