October 14, 2016 at 8:31 am
Hello all,
I guess I can be called a Newbie as I am pretty new to this forum, and not formally trained in SQL... So looking for direction as well as best practices for what I am trying to do.
Long story short, I have data for the various offices we have in Canada. This data (for the most part) pertains to the facilities management of staff, seating and office space. One of the reports generated creates customized phone lists for the various offices. As it is now, I grab the needed fields to populate the report, and create a field (called Heads) that grabs the first letter of the last names for grouping (generates dividers for each letter).
My problem is this. Some of the offices want the phone lists sorted differently. Some want it sorted by floor, some want it sorted by first name. so right now my SQL Select statement in my stored procedure looks like this:
SELECT DISTINCT
TOP (100) PERCENT dbo.StaffListings.LocCode, dbo.StaffListings.AutoNumber, dbo.StaffListings.OfficeNumber, dbo.StaffListings.[Column], dbo.StaffListings.Floor,
LEFT(dbo.StaffListings.LastName, 1) AS Heads, dbo.StaffListings.LastName, dbo.StaffListings.ShowName, dbo.StaffListings.FirstName, dbo.StaffListings.Extension,
LEN(dbo.StaffListings.Extension) AS ExtLen, dbo.StaffListings.[10DigitPhone], dbo.StaffListings.Department, dbo.StaffListings.FirstAid, dbo.StaffListings.FireWarden, dbo.StaffListings.SpecialStatus,
1 AS [Dummy], dbo.OfficeCodes.Address1, dbo.OfficeCodes.Address2, dbo.OfficeCodes.City, dbo.OfficeCodes.StateProv, dbo.OfficeCodes.PostalZip,
dbo.OfficeCodes.PhoneNo, dbo.OfficeCodes.FaxNo, dbo.StaffListings.TempFlex, dbo.OfficeCodes.SharepointPhone
FROM
dbo.StaffListings INNER JOIN
dbo.OfficeCodes ON dbo.StaffListings.LocCode = dbo.OfficeCodes.LocCode
WHERE
(((dbo.StaffListings.LocCode) Like @pLocCode) AND (NOT (dbo.StaffListings.LastName LIKE N'0ConfRoom')) AND (NOT (dbo.StaffListings.LastName LIKE N'0RoomExtra')) AND
(NOT (dbo.StaffListings.LastName LIKE N'0PhoneExtra')) AND (NOT (dbo.StaffListings.LastName LIKE N'0Emerg')) AND (LEN(dbo.StaffListings.Extension) > 0))
ORDER BY
dbo.StaffListings.LastName, dbo.StaffListings.ShowName
my first thought was to use Dynamic SQL (reading up on it now, but seems there are a lot of warnings on it) to pass the definitions for Heads and the ORDER BY sections. But then I though, could I pass the whole SQL string to the stored procedure? Would there be a problem with the latter?
I'm guessing the @SQL would be something like:
SET @SQL = 'SELECT DISTINCT ' + @pSelect + ' ORDER BY ' + @pOrder
where @pSelect is the above select statement (minus the ORDER BY) and the @pOrder would be the remainder.
October 14, 2016 at 8:41 am
There's a big risk of SQL injection if you do it that way. Can you not have your presentation layer (Excel spreadsheet, SSRS or whatever) do the sorting? And, if the columns requested don't vary much from office to office, just return all columns in your query and have the presentation layer decide which ones to display.
John
October 14, 2016 at 9:24 am
Agree
I've done this sort of thing using the BI tool (for me, Cognos or SSRS)
Add a prompt for what you want to sort it by and present accordingly
It's not that difficult to automate if you want to deliver the same data ordered based upon the users requirement on a regular basis
- Damian
October 14, 2016 at 9:40 am
Read this: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail talks about catch-all queries.
October 14, 2016 at 1:22 pm
I guess I could make different 'Heads' for each column that could be used for the sort. Really, I don't think it would be more than 3 or 4 different fields. I'm sure I know how to programmatically group and sort in the report opening (which is used for the print outs).
Guess I was just trying to be fancy...
[EDIT]
I take that back, it might not be all that easy (the report layout was made quite a while ago, so I had to go back and look at it).
Maybe the whole process I started is not the best... In any case, the report takes the Heads field for spacers between the groupings. I'm guessing I should take this back to an access forum, but just in case you all might have a suggestion, I'm guessing that I could take the output from the SQL and create a query that would use the sorting Header field as Heads. I could then do the Order By and Grouping before the report opens...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply