Determining Stored Procedure Result Columns?
-
can this be done??? I would like to make a Stored Procedure that builds a Select statement, based on the results of another SP (which could change)
-
quote:
can this be done??? I would like to make a Stored Procedure that builds a Select statement, based on the results of another SP (which could change)Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Sorry for the blank post.
Yes. You can take the output of one sp, and place it into a temporary table. Then process through that temporary table building dynamic SQL to build the select statement you desire.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Hey Greg, thanks for the reply. I looked at your SQL Server Examples page, but couldn't figure out what example were you referring to that would help out with this task. ? I thought I could dump the SP results into a temp table, but have had trouble figuring out how. Is it necessary to create the table first?
-
I have yet to put one out on the web. Try this example:
create table #tmpwho (
spid int,
ecid int,
status char(20),
loginame char(100),
hostname char(100),
blk int,
dbname char(100),
cmd char(100))
go
INSERT INTO #Tmpwho EXECUTE sp_who
go
select * from #tmpwho
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
still searching...
Unfortunately, the code you included in your last post assumes that I already know the Result COLUMNS of my SP. I don't have any idea what the columns that will be returned are. I don't actually care about the data that is returned. All I really care about are the names of the columns (Headers).
-
Here is something I think might work for you:
select * into #tmptest
FROM OPENROWSET('SQLOLEDB','<yourserver>';'<user>';'<password>',
'exec sp_who ') AS a
select column_name from tempdb.information_schema.columns where table_name like '#tmptest___%'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Thank You, Greg!That's what I was looking for. I think you saved me a couple hours of headache there! I am surprised that this was not one of the FAQs. This is not the first time I have wanted this functionality.
OF course, as soon as I got this figured out, we realized it won't be necessary.
But, now I know, and knowing is at least half of the battle. -
quote:
Here is something I think might work for you:select * into #tmptest
FROM OPENROWSET('SQLOLEDB','<yourserver>';'<user>';'<password>',
'exec sp_who ') AS a
select column_name from tempdb.information_schema.columns where table_name like '#tmptest___%'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
-
Hi Greg,
I've run into the same problem and when applying your solution I get an error message. Could u please take a look at it and give me a hint what am I doing wrong here...
/////////////////////
USE ct10002G
GO
select * into #tempalex
FROM OPENROWSET('SQLOLEDB','N1244';'sa';'','exec DRFExportOMC')
a
select column_name from tempdb.information_schema.columns where table_name like '#tempalex___%'
////////////////////////////////
It rerurns the following error:
Server: Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'DRFExportOMC'.
Thanx much in advance,
Alex
P.S. Here is the stored procedure:
Create Proc dbo.DRFExportOMC (
@i_AltId tinyint,
@i_NelId smallint
)
As
Begin
/**********************************************************
*Procedure Name: DRFExportOMC
*Database: CT2000_V14
*Server: CBALARD-2
*SQL-Builder4.1
*File Path:
* M:\ct2_ct2gsm_KRNObj_dev_ct2gsm_v14_00_cbalard\ccase_ct2000\CT2000\GSM\KRNObjects\DRF\SRC\SQL\DRFExportOMC.pro
*********************************************************/
IF @i_Nelid IS NULL SET @i_Nelid = @i_Altid * 4
SELECT O.omcVersion,
CAST( 0 AS binary(4) ) as eqtVersion,
O.OMC,
O.CooRef,
O.TemplateRef,
O.omcArchitecture,
O.omcLanguage,
O.omcVersion,
O.pcmKind,
O.algoA,
O.cellTiering,
O.GPRS,
O.ASCIMode,
O.ss7Protocol,
O.gsmProtocol,
O.microAlgoType,
O.comment
FROM DRFOmcView AS O
INNER JOIN NELNetworkElementListItemOMCView AS NEL
ON NEL.Altid = O.Altid
AND NEL.rowId = O.rowId
WHERE @i_AltID = O.altId
AND IsGhost = 0-- OMC ghost are not exported
AND NEL.nelid = @i_NelId
END
quote:
Here is something I think might work for you:select * into #tmptest
FROM OPENROWSET('SQLOLEDB','<yourserver>';'<user>';'<password>',
'exec sp_who ') AS a
select column_name from tempdb.information_schema.columns where table_name like '#tmptest___%'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
-
Try specifying the Database and Owner of the stored procedure e.g.
EXEC pubs.dbo.DRFExportOMC
-
Tnanx for the reply, it did work. ... but now I'm coming up with the following error:
**************************************
USE ct10002G
GO
select distinct * into #tempalex
FROM OPENROWSET('SQLOLEDB','N1244';'sa';'','exec CT10002G.dbo.DRFExportOMC @i_AltId = "0", @i_NelId = "0" ')
a
select distinct column_name from tempdb.information_schema.columns where table_name like '#tempalex___%'
**********************************
Server: Msg 2705, Level 16, State 3, Line 2
Column names in each table must be unique. Column name 'omcVersion' in table '#tempalex' is specified more than once.
Tnanx much,
Alex
quote:
Try specifying the Database and Owner of the stored procedure e.g.EXEC pubs.dbo.DRFExportOMC
-
Alex,
the select statement in your stored procedure lists O.OMCVersion twice (col 1 & 8). If you actually need the same value twice, try aliasing the second instance of the column with an " as OMCVersion2"
-
THANX, I'm still havin' some troubles with this sp. At the moment I have the query that gets the result from another stored procedure (DRFExportOMC) and outputs all the column names needed, but it shows them sorted by ktt.label column... at the same time I don't need the output to be sorted at all (to actually have them in the sequence they come out in the initial stored procedure output. Like this:
/**********************************************************
The initial stored procedure: DRFExportOMC
*********************************************************/
CREATE Proc dbo.DRFExportOMC (
@i_AltId tinyint,
@i_NelId smallint
)
As
Begin
IF @i_Nelid IS NULL SET @i_Nelid = @i_Altid * 4
SELECT distinct O.omcVersion,
CAST( 0 AS binary(4) ) as eqtVersion,
O.OMC,
O.CooRef,
O.TemplateRef,
O.omcArchitecture,
O.omcLanguage,
O.pcmKind,
O.algoA,
O.cellTiering,
O.GPRS,
O.ASCIMode,
O.ss7Protocol,
O.gsmProtocol,
O.microAlgoType,
O.comment
FROM DRFOmcView AS O
INNER JOIN NELNetworkElementListItemOMCView AS NEL
ON NEL.Altid = O.Altid
AND NEL.rowId = O.rowId
WHERE @i_AltID = O.altId
AND IsGhost = 0-- OMC ghost are not exported
AND NEL.nelid = @i_NelId
END
/* The output comes out with the following column names sequence.... (from left to right, just like in any table */
omcVersion
eqtVersion
OMC
CooRef
TemplateRef
omcArchitecture
omcLanguage
pcmKind
algoA
cellTiering
GPRS
ASCIMode
ss7Protocol
gsmProtocol
microAlgoType
comment
When I run my final query:
USE ct10002G
GO
drop table #tempalex
select distinct * into #tempalex
FROM OPENROWSET('SQLOLEDB','N1244';'sa';'','exec CT10002G.dbo.DRFExportOMC @i_AltId = "1", @i_NelId = "1"')
as a
select distinct tisc.column_name
from tempdb.information_schema.columns as tisc
join PDSKeywordTranslationTable as ktt
on tisc.column_name = ktt.keyword
join PDSParameterDefinitionTable as pdt
on pdt.keyword = ktt.keyword
where tisc.table_name like '#tempalex___%' and pdt.module = 'GSM'
/* The output is as follows:
column_name
---------------------------------------------
omcVersion
ss7Protocol
OMC
omcLanguage
gsmProtocol
microAlgoType
algoA
omcArchitecture
ASCIMode
pcmKind
CooRef
(11 row(s) affected)
As u can see the ORDER BY is performed as if it was set up to be done by default. Is there a way to solve it? Pls help, if anyone ran into this before...
Thnx in advance,
Alex
quote:
Alex,the select statement in your stored procedure lists O.OMCVersion twice (col 1 & 8). If you actually need the same value twice, try aliasing the second instance of the column with an " as OMCVersion2"
-
Alex,
If I'm understanding you right, you want the final output to be in column order from the original stored procedure.
To do that, simply add an order by clause to your final query:
select distinct tisc.column_name
from tempdb.information_schema.columns as tisc
join PDSKeywordTranslationTable as ktt
on tisc.column_name = ktt.keyword
join PDSParameterDefinitionTable as pdt
on pdt.keyword = ktt.keyword
where tisc.table_name like '#tempalex___%' and pdt.module = 'GSM'
ORDER BY TISC.ORDINAL_POSITION
Scott
Thank You, Greg!
But, now I know, and knowing is at least half of the battle.Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply