Question on SQL sp getting correct results back

Question

Post reply

Question on SQL sp getting correct results back

JJ-251245

SSC Veteran

Points: 233
More actions
August 24, 2005 at 8:39 am

#87329

I have a stored procedure which gets passed 2 variables into it. This works fine, however I don't see everything I need in my results.
Basically what I'm trying to get in my results is all of the let's say 'Page Types' for a project (used to run sp) to show up with the correct counts for each PageType and Statusname for that project.
I tried changing one of my joins to the left join to get all pagetypes but still doesn't show me what I need. I think one of my other joins is causing the problem. I have post the SP below.
CREATE PROCEDURE sp_RequestProj_Legend
@Projects_Name varchar(50),
@Users_pkey int
AS
DECLARE @PageTypeAbbrev varchar(5), @SQLStr nvarchar(4000)
DECLARE GetRows CURSOR FOR
SELECT distinct ProjLegend_abbrev FROM dbo.Project_Legend ORDER BY ProjLegend_abbrev
SET @SQLStr = 'SELECT rpt.pagetype, rpt.PageFontColor, rpt.PageTypeAbbrev, count(rh.Project_requestheader_pkey) as TotalPagetype, p.projects_pkey'
OPEN GETROWS
FETCH NEXT FROM GETROWS INTO @PageTypeAbbrev
While @@FETCH_STATUS = 0
BEGIN
  SET @SQLStr = @SQLStr + ',SUM(CASE WHEN pl.ProjLegend_abbrev= ''' + @PageTypeAbbrev + '''
  THEN 1 else 0 END) as ' + @PageTypeAbbrev
  FETCH NEXT FROM GETROWS INTO @PageTypeAbbrev
END
CLOSE GETROWS
DEALLOCATE GETROWS
Set @SQLStr = @SQLStr + ' FROM dbo.Project_RequestPageType   rpt LEFT JOIN
   dbo.Project_RequestHeaders rh ON rh.PageType = rpt.PageType_Pkey INNER JOIN
                      dbo.Project_RequestStatus prs ON rh.Project_RequestStatus_pkey = prs.Project_RequestStatus_pkey LEFT JOIN
                      dbo.Projects p ON rh.Project_Pkey = p.Projects_Pkey INNER JOIN
                      dbo.Projects_Users pu ON p.Projects_Pkey = pu.Projects_Pkey INNER JOIN
                      dbo.Project_Legend pl ON prs.StatusName = pl.StatusName
        WHERE     (prs.RequestCompleted = 0) AND p.Projects_Name= ''' + @Projects_Name + '''
        AND pu.Users_pKey= ' + CONVERT(varchar,@Users_pkey) + '
        GROUP BY rpt.PageTypeAbbrev, rpt.pagetype, rpt.PageFontColor, p.projects_pkey
        ORDER BY rpt.PageTypeAbbrev'
EXEC sp_ExecuteSQL @SQLStr
GO
Results of sp run above (I should see Login Request, Problm Report and Change Request with all results if none, then 0, but I only see 2 of the 3. I hope this helps explain a little bit.)
Login Request Purple LR 2 25 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Problem Report Steelblue PR 2 25 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1
Any input or suggestions would be great appreciated on what I might be doing incorrectly or could do differently.
JJ

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

First off... why are you using dynamic sql for this??

JJ-251245 SSC Veteran Points: 233 More actions · Answer 2

The reasoning behind that is because I was trying to get just one row with all elements for each PageType instead of having for example: 15 records for a pagetype (Change Request - CR). I originally didn't do this dynamically and weing that this was being used in a web application, it was taking the page approx. 30 seconds to load because it took this and did calcuations and then showed the page. So the other reason is timing. how long it took to generate the actual dataset from calculations (I know only count), but for displaying within a web application.

I'm still pretty junior even though I've been writing SQL for about 5 years now. There are still things I'm learning and would appreciate any feedback maybe to explain why/how I could do this another way that might be more efficient? Thanks in advance.

Vasc SSCarpal Tunnel Points: 4623 More actions · Answer 3

You can eliminate your cursor

SET @SQLStr = 'SELECT rpt.pagetype, rpt.PageFontColor, rpt.PageTypeAbbrev, count(rh.Project_requestheader_pkey) as TotalPagetype, p.projects_pkey'

SELECT @SQLStr = @SQLStr + ',SUM(CASE WHEN pl.ProjLegend_abbrev= ''' + PageTypeAbbrev + '''

THEN 1 else 0 END) as ' + PageTypeAbbrev

FROM ( SELECT distinct ProjLegend_abbrev as PageTypeAbbrev

FROM dbo.Project_Legend

ORDER BY ProjLegend_abbrev) Tbl

as for your join you need to post DDL and sample data

Kindest Regards,

Vasc

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

We need DDL scripts for the tables involved (at least the important parts), and scripts that inserts testdata that when we use the query you have, will produce the 'wrong' result. Along with this, you should present how you'd like the result to look in comparison with the current results.

For this to work, it's also crucial that the testdata is 'authentic' - ie that it follows any business rules you may have in place. On the other hand, a few rows, just enough to demonstrate should be enough, you don't have to gut your entire database for it.

/Kenneth