Select columns from result set of stored procedure
sql server stored procedure return resultset
insert into temp table from a stored procedure that returns multiple result sets
how to use results from one stored procedure in another
sql server stored procedure return multiple result sets
how to get the resultset datatypes of a stored procedure
mysql stored procedure result set into variable
insert specific columns from stored procedure into #temp table
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'.
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
Select columns from result set of stored procedure, 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 “How to select columns from Stored Procedure Resultset?” Though Stored Procedure has been introduced many years ago, the question about retrieving columns from Stored Procedure is still very popular with beginners. Let us see the solution in quick steps. First we will create a sample stored procedure. CREATE PROCEDURE SampleSP AS SELECT 1 AS Col1, 2 AS Col2 UNION SELECT 11, 22 GO
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.)
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.
SQL SERVER, It is fun to go back to basics often. Here is the one classic question: "How to select columns from Stored Procedure Resultset?" Though Stored The challenge we usually face with this kind of stored procedure is that whatever columns are available in the result set, stored procedure will return the same number of columns as an output and not the selected/required information. Let me take a sample stored procedure from AdventureWorks2012 database and view its result set.
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
SQL Server EXECUTE Statement with RESULT SET, SQL Server 2012 introduced a RESULT SET clause to the EXECUTE statement. It can be used to specify alternate data types and column names for result sets returned by an EXECUTED statement or Stored Procedure. --Rename and retype results from a SELECT statement EXEC ('SELECT My question: is it possible to pass in * to select all columns although the procedure is asking for a specific column name? Edit: Unfortunately I think I may have worded my question poorly. I was looking for a way to see if my stored procedure could tell if I wanted to select all records from the table, or just specific columns from a record.
This works for me: (i.e. I only need 2 columns of the 30+ returned by
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. ("An INSERT EXEC statement cannot be nested.")
sql server, 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 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
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
insert into #test_table EXEC MyStoredProc 'param1', 'param2'
Now you fetch the value from
select col1,col2....,col80 from #test_table
sp_describe_first_result_set (Transact-SQL), Returns an empty result set if the batch returns no results. This will return base table names as the source column information. If the procedure is called from Transact-SQL, the return value is always zero, sp_describe_first_result_set @tsql = N'SELECT object_id, name, type_desc FROM sys.indexes'. This feature allows us to modify the column names and column data types of the result sets returned by a stored procedure without actually modifying the stored procedure code. In a real world scenario, we have a legacy stored procedure which shows a result set with one column “CustomerName” having varchar(50) as output, and it is being
Save SQL Server Stored Procedure Results to Table, Copying a SQL Server Stored Procedure's Results Set to a Local Temp for the selected columns from the SalesOrderHeader table match the Here is a simple script which demonstrates how we can insert the result of the stored procedure into a temporary table without pre-creating the temporary table before the stored procedure is executed. However, before we do this task, we have to do to enable ad hoc distributed queries. Step 0: Create a Stored Procedure
Execute a stored procedure in a SELECT or FROM statement or a , 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,.. SELECT), the rows specified by the SELECT statement will be sent directly to the client. For large result sets, the stored procedure execution will not continue to the next statement until the result set has been completely sent to the client. For small result sets, the results will be spooled for return to the client and execution will continue.
Select columns from result set of stored procedure - sql-server, With this setting, when a stored procedure is executed, SQL Server will follow all possible logic paths until it has SQL Server will return the format of the first select! The column information is returned as a normal result set. Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable. 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.
- Out of curiosity, does this query work: SELECT * FROM EXEC MyStoredProc 'param1', 'param2' If so, what column names does it display in the result set, and can you use those column names in your select list?
- I never did find an answer for this.
- Well you never answered a very important question! What SQL platform are you asking about? MySQL, Microsoft SQL Server, Oracle, etc. It looks to me like it's SQL Server, but you need to tell people or they can't reliably answer your question.
- Well, it must be MS-SQL.
EXECisn't a MySQL keyword (the MySQL equivalent is prepared statements). Although I'd like to know the answer for MySQL, the answers below target T-SQL. Retagging.
- I never did find an answer for this
- It also doesn't work when you don't know the table definition
- didn't know about that type. Are they implemented the same as temp tables? Or is it strictly in memory?
- This was interesting: sqlnerd.blogspot.com/2005/09/…
- This works okay if the number of columns supplied in the temp table is the same as those in the output of the stored procedure. chagbert.
- I think the OPENQUERY suggestion in that link is much closer to what the OP is looking for.
- @LawfulHacker Holy smokes. What are you doing on SQL Server 2000 in the year 2014?
- Big corporations with legacy systems :D
- I've had 13 single quotes in a row before in dynamic-sql-that-generated-dynamic-sql-that-generated-dynamic-sql...