Exec in Sp and returning recordset

[Jeff Davies]

Guys,
I am trying to Execute the following in a stored proc.

exec spCreateTmpTable @Param1, @Param2

SELECT TOP 100 PERCENT dbo.tblCustomers.*
FROM dbo.tblCustomers INNER JOIN
dbo.TmpTable ON dbo.tblCustomers.ID = dbo.TmpTable.ID


These two things work fine individually.
spCreateTmpTable creates the Temp Table and fills it with appropriate data.

The Select SQL will then happily work in a view,
but when I try and run them both in the one SP, I get no records returned.
What am I missing?
Thanks in advance,
Jeff

 

[ctcraig]

The first SP calls the second which creates the Temp table, Right? If
that is the case the Temp table goes away when the called SP finishes
executing...

 

[Jeff Davies]

Thanks Craig, but the table is not a # table and stays permanently.
That is not the problem.
The table is certainly available after the first sp runs.
Any other ideas?
J

 

[Malcolm Cook]

try putting a 'set nocount on' at the begininning of your stored proc (c.f.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev01/html/vb01a1.asp)

let us know.

 

[llat.com]

try putting a 'set nocount on' at the begininning of your stored proc
(c.f.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev01/html/vb01a1.asp)

let us know.