How can I change this stored procedure to get the desired result

Question

Post reply

How can I change this stored procedure to get the desired result

Guzman-658444

Old Hand

Points: 347
More actions
December 19, 2008 at 7:59 am

#195358

I have this stored procedure below:
------------------------------------------------------
DECLARE
@strSQL nvarchar(2000),
@strWhere varchar(1000)
Set @strSQL = '
Select tlkResolution.resolution,
Case
When sq.CaseCount Is Null Then 0
Else sq.CaseCount
End As CaseCount
From tlkResolution
Left Join (
Select tblCaseInfo.resolutionID, Count(Distinct tblCaseInfo.caseID) As CaseCount
From tblCaseInfo
LEFT JOIN tblSessions on tblCaseInfo.CaseID = tblSessions.CaseID'
If @Filter <> '' --Check for filter value
Begin
Set @strSQL = @strSQL + ' Where ' + @Filter
End
Set @strSQL = @strSQL + '
Group By tblCaseInfo.resolutionID
) sq On sq.resolutionID = tlkResolution.resolutionID'
------------------------------------------------------------
What I would like to do is to get the below condtion into the stored procedure:
get tblCaseInfo.CNICRegion that is equal to tblLocalEvaluatorActivities.Details
where tblLocalEvaluatorActivities.Activity = 'CNIC' AND tblLocalEvaluatorActivities.UserID = '341'
Any help is greatly appreciated.
Thanks,
Arun

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Arun

as regular SQL it will look like this...

SELECT r.resolution, ISNULL(sq.CaseCount, 0) As CaseCount

FROM tlkResolution r

LEFT JOIN (SELECT c.resolutionID, Count(Distinct c.caseID) As CaseCount

FROM tblCaseInfo c

INNER JOIN tblLocalEvaluatorActivities a ON a.Details = c.CNICRegion AND a.Activity = 'CNIC' AND a.UserID = '341'

LEFT JOIN tblSessions s on c.CaseID = s.CaseID -- why?

WHERE 1 = 1 -- filter

GROUP BY c.resolutionID

) sq On sq.resolutionID = r.resolutionID

... but a couple of questions first:

Why the join to tblSessions? I can't see that it does anything

What does your filter look like?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Guzman-658444 Old Hand Points: 347 More actions · Answer 2

Chris,

I added the tblSessions to take care of an error in the ASP report, a little while ago.

Here is the snippet of the filter that is sent in:

one of the cases could be:

strFilterResp = strFilterResp & " AND (tblCaseInfo.CNICRegion = " & intCNIC & ")"

another case could be:

strFilterResp = strFilterResp & " AND (tblCaseInfo.Echelon1 = " & strEchelon1 & ")"

etc.,

tblLocalEvaluatorActivities has the structure:

CREATE TABLE [dbo].[tblLocalEvaluatorActivities](

[UserId] [int] NULL,

[Activity] [varchar](350) NULL,

[Details] [varchar](max) NULL

) ON [PRIMARY]

Activity could be CPOID, CNIC, Echelon1, Echelon2 or Echelon3

Details has codes like MM, MJ that pertain to CPOID etc.

Details has codes like NDW, CNRE .. that pertain to CNIC Region etc.

I need to account for these depending on the filter sent in.

Thanks,

Arun