TEMP TABLE NOT WORKING - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

TEMP TABLE NOT WORKING

Author

Topic

sanjeet
Starting Member

5 Posts

Posted - 2006-01-17 : 18:00:44

In query analyzer the correct data is returned.

But when I run it in my application no records ae displayed.

In Sql profiler it says completed and I use the values in query analzyer it works fine. here is my stored proc:

CREATE PROCEDURE [dbo].oc_OnlineCaseOrder

@CategoryId int,
@Order varchar(25)

As

SET @CategoryId = @CategoryId
SET @Order = @Order

If @Order = 'Alpha'

Begin

SELECT
oc.[CaseId],
oc.[StatusId],
oc.[CategoryId],
oc.[Title],
oc.[CaseText],
oc.CourseId,
occ.Description AS CategoryDescription,
ocs.Description AS StatusDescription
FROM
[dbo].oc_OnlineCase oc WITH (nolock)
JOIN dbo.oc_OnlineCaseCategory occ WITH (nolock) ON oc.CategoryId = occ.CategoryId
JOIN dbo.oc_OnlineCaseStatus ocs WITH (nolock) ON oc.StatusId = ocs.StatusId
WHERE oc.CategoryId = ISNULL( @CategoryId, oc.CategoryId )
AND oc.StatusId = 100
Order by oc.[Title]

END

ELSE

Begin

DECLARE @TempCaseIds Table
(
CaseId int,
CategoryDescription varchar(30)
)

SELECT MIN(oca.AuditDate) as "Recent Case Publication", oca.CaseId, occ.[Description]
INTO #TempCaseIds
From
[dbo].oc_OnlineCaseAudit oca WITH (nolock)
JOIN dbo.oc_OnlineCase oc WITH (nolock) ON oc.CaseId = oca.CaseId
JOIN dbo.oc_OnlineCaseCategory occ WITH (nolock) ON oc.CategoryId = occ.CategoryId
JOIN dbo.oc_OnlineCaseStatus ocs WITH (nolock) ON oc.StatusId = ocs.StatusId
WHERE oc.CategoryId = ISNULL( @CategoryId, oc.CategoryId )
AND oc.StatusId = 100
Group by oca.CaseId,occ.[Description]

Select DISTINCT
CaseId, [Description]
From #TempCaseIds

End

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-17 : 18:17:42

If it runs fine in Query Analyzer, then the problem is inside your application and not within the stored procedure. We would need to see the application code to help as the stored procedure code is working fine.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources