Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mori0043
Starting Member
18 Posts |
 Posted - 2006-07-09 : 22:01:26
|
| I have a stored procedure that stores values in a temp table and then returns a result set. When I execute the command in query analyzer I can see the result set, but when I execute the stored proc from ASP OLE DB object nothing is returned... Can someone help please?Here is the stored proc:CREATE PROCEDURE sproc_SECTIONS_BY_PORTAL @PORTAL_KEY INTAS CREATE TABLE #sections_ordered ( SectionKey INT ) --add a record for each portal in the portalsectionxreftable DECLARE @TEMP_SECTION_KEY INT DECLARE c1 CURSOR FOR -- Get a list of the parent sections SELECT PSX.SectionKey FROM PortalSectionXref PSX INNER JOIN Sections S ON S.SectionKey = PSX.SectionKey WHERE PSX.PortalKey = @PORTAL_KEY AND S.ParentKey = 0 ORDER BY PSX.SortOrder OPEN c1 FETCH NEXT FROM c1 INTO @TEMP_SECTION_KEY WHILE @@FETCH_STATUS = 0 BEGIN --first insert the parent row INSERT INTO #sections_ordered VALUES(@TEMP_SECTION_KEY) INSERT INTO #sections_ordered SELECT PSX.SectionKey FROM PortalSectionXref PSX INNER JOIN Sections S ON S.SectionKey = PSX.SectionKey AND PSX.PortalKey = @PORTAL_KEY WHERE S.ParentKey = ( SELECT SectionKey FROM Sections S2 WHERE SectionKey = @TEMP_SECTION_KEY ) ORDER BY PSX.SortOrder FETCH NEXT FROM c1 INTO @TEMP_SECTION_KEY END CLOSE c1 DEALLOCATE c1 SELECT CASE S.ParentKey WHEN 0 THEN '' ELSE '->' END as Subsection, ParentKey, SectionNameE, SectionNameF, ActiveFlag FROM #sections_ordered SO INNER JOIN Sections S ON S.SectionKey = SO.SectionKey DROP TABLE #sections_orderedGO |
|
|
mori0043
Starting Member
18 Posts |
Posted - 2006-07-09 : 22:49:19
|
| I figured it out... I had to set no count onn when I did the insert and turn it back off when I did the select. |
 |
|
|
|
|
|
Stored proc with nothing returned