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'.
This question is related to
sql-server
tsql
select
stored-procedures
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 <> 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
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.
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/
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.
(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.
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
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.
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.
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
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
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
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!
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
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<string>("col1"),
Col2 = row.Field<string>("col2"),
}).Dump();
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.
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.
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.
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. ("An INSERT EXEC statement cannot be nested.")
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
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
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.
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)
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.
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. ("An INSERT EXEC statement cannot be nested.")
(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.
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
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
(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.
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.
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)
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
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!
(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.
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
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<string>("col1"),
Col2 = row.Field<string>("col2"),
}).Dump();
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
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 <> 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
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
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/
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
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
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
Source: Stackoverflow.com