[SS2K5] : Populate a CTE from stored procedure result set

Hi,

I'd like to know if it's possible to populate/load a CTE from a stored procedure result set ?

Kind of :

USE MyDB;
GO
WITH MyCTE (Col1, Col2, Col3)
AS
(

EXECUTE myStoredProc

)
SELECT *
FROM MyCTE
GO

Thanks for your help.

Cheers,

Bertrand

A couple of options include loading your stored procedure output to a temp table:

INSERT NTO #aTempTable
EXEC yourStoredProcedure

and also converting your procedure into an function or perhaps a view and then you can join to the function or view. If the primary objective of the stored procedure is to be used as you describe it might be best from the outset to consider converting it into a function.

But also keep in mind that in many cases the temp table / stored procedure option will outperform the function.

|||

CTE in SQL Server 2005 is just syntactic sugar. It is similar to view in that the query expression of the CTE is parsed into the original query, compiled, optimized and executed. So there is no special optimization in terms of storing intermediate results in case of multiple references to the same CTE and so on. So you have few options:

1. Convert the SP to inline TVF - best performance

2. Convert the SP logic to a view

3. Convert the SP to non-inline TVF

4. Use a temporary table to store results from SP and reuse it

|||

Well in fact I have to perform a row by row operation on the sp result set. Which means using a cursor. And I know that using a cursor with a temporay table is not the best in term of performance so I thought that CTE was the best option...

But your first option could fits for my problem.

Thanks for your guidance.

|||What kind of row by row operation are you performing?|||

Well in fact I received a result set from one stored procedure. Each column in this result set become input parameters for an other stored proc I have to call.

It could be more "easy" if I had the right to modifiy the both of these stored proc but I'm not allowed to and I just can to plug my logic between these sp ....

[Microsoft][ODBC Text Driver] In the file specification '...', the Col1 option is invalid.

I'm trying to connect to a tab-delimited text file using the ODBC data access. I set it up using Administrative Tools - Data Sources (ODBC) from the start menu, and clicked the 'Guess' button under Define Format.

The schema it created looks like this:

[cims_output_data.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
CharacterSet=OEM
Col1=SIMNAME
Col2=TIMESTAMP
Col3=SIMTYPE
Col4=REGION
Col5=SECTOR
....etc.

It looks OK, but if I go back to Define in Administrative Tools and try to review it, I get a similar error message:

Ini File (or Registry) C:\Program Files\CIMS\schema.ini is corrupt.
Section: cims_output_data.txt, Key: Col1.

Has anyone seen this before? There is nothing on MSDN as far as I could find. I read on another forum that if you replace the label 'Col1' with 'Col' the problem is solved but it still reports that the schema is corrupt when I try to open it in Administrative Tools.

Bill.

Hi Bill,

Could you try specifying the types of your columns in the schema (i.e. Col1=SIMNAME string...)?

I hope this helps,

John Gordon (MSFT)

|||

John

thanks, I tried that. The funny thing is switching the "Col1" label to "Col" really does solve the problem so I am just going with this solution and ignoring the fact that the ODBC configuration editor says the schema is corrupt.

Bill.

[Microsoft][ODBC Text Driver] In the file specification '...', the Col1 option is invalid.

I'm trying to connect to a tab-delimited text file using the ODBC data access. I set it up using Administrative Tools - Data Sources (ODBC) from the start menu, and clicked the 'Guess' button under Define Format.

The schema it created looks like this:

[cims_output_data.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
CharacterSet=OEM
Col1=SIMNAME
Col2=TIMESTAMP
Col3=SIMTYPE
Col4=REGION
Col5=SECTOR
....etc.

It looks OK, but if I go back to Define in Administrative Tools and try to review it, I get a similar error message:

Ini File (or Registry) C:\Program Files\CIMS\schema.ini is corrupt.
Section: cims_output_data.txt, Key: Col1.

Has anyone seen this before? There is nothing on MSDN as far as I could find. I read on another forum that if you replace the label 'Col1' with 'Col' the problem is solved but it still reports that the schema is corrupt when I try to open it in Administrative Tools.

Bill.

Hi Bill,

Could you try specifying the types of your columns in the schema (i.e. Col1=SIMNAME string...)?

I hope this helps,

John Gordon (MSFT)

|||

John

thanks, I tried that. The funny thing is switching the "Col1" label to "Col" really does solve the problem so I am just going with this solution and ignoring the fact that the ODBC configuration editor says the schema is corrupt.

Bill.

[help] Possible to script a diff into SQL Query?

My goal is to add a diff into a query that grabs data from 2 different tables.

The code:
SELECT
MIN(TableName) as TableName,
ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
FROM
(
SELECT 'Table A' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21

from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769

UNION all

SELECT 'Table B' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21

from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
) tmp

GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
HAVING COUNT(*) = 1
ORDER BY 2,1

Is it possible to add a command into the query to output diff/compare scenario?

Thanks in advance for any help.

If you are using SQL Server 2005 you might be able to use the EXCEPT operator instead of the UNION ALL operator. Give a look to the EXCEPT operator in books online.

If the EXCEPT operator looks like it does what you want and it does not perform to your satisfaction, come back and talk to us again. While the EXCEPT operator might be conceptually the most straight forward approach it is not always the most efficient.

Kent

|||Yes, we're using 2005. I'll look into using the EXCEPT op. thanks for the advise, Kent.