June 21, 2010 at 8:30 pm
I have a table populated with values, that I would like to display differently for different users.
The most simple solution that occured to me is to add a column to the table to filter from this value.
However, in the past I have populated two functions and called the appropriate function list based on a parameter.
Is it possible to wrap all this up in a single procedure?
I have table 'AuditTypes' with columns AuditTypeId (int) and AuditType nvarchar(100)
1. Internal
2. External
3. Regulator
4. Special Regulator
5. Accounting
6. Health & Safety
7. Production
8. Laboratory
9. Maintenance
If the user parameter is 0 then I return all AuditTypes (with an entry for 0 AS "(All)") - from Function AuditTypesAll
If the user parameter is 1 then I only return entries from 5, 6, 7, 8 and 9 - from Function AuditTypesSelected
SELECT CASE WHEN @CheckAuditor = 0 THEN AuditType2 ELSE AuditType1 END AS UserAuditType
FROM dbo.AuditTypesAll_S() CROSS JOIN
dbo.AuditTypesSelected_S()
GROUP BY CASE WHEN @CheckAuditor = 0 THEN AuditType2 ELSE AuditType1 END
Thanks for your time - look forward to your thoughts
June 22, 2010 at 2:04 am
Hi there,
I'm just trying to help.. You may use correlated subquery to filter the result depending on the parameter passed.. Here's my solution:
DECLARE @AuditTypes TABLE (AuditTypeId INT, AuditType NVARCHAR(100))
DECLARE @CheckAuditor INT
SET @CheckAuditor = 1
INSERT INTO @AuditTypes
SELECT 1,'Internal' UNION ALL
SELECT 2,'External' UNION ALL
SELECT 3,'Regulator' UNION ALL
SELECT 4,'Special Regulator' UNION ALL
SELECT 5,'Accounting' UNION ALL
SELECT 6,'Health & Safety' UNION ALL
SELECT 7,'Production' UNION ALL
SELECT 8,'Laboratory' UNION ALL
SELECT 9,'Maintenance'
SELECT AuditTypeId, AuditType
FROM @AuditTypes at
WHERE EXISTS (SELECT 1 FROM @AuditTypes at2
WHERE at.AuditTypeId=at2.AuditTypeId
AND ((at2.AuditTypeId IN (5,6,7,8,9) AND @CheckAuditor=1)
OR (@CheckAuditor=0)))
Please let me know if you need further assistance.. 😀
June 22, 2010 at 2:21 am
Hi again..
You may eliminate cross join with correlated subquery, here's my other solution..
SELECT AuditTypeId, AuditType
FROM @AuditTypes at
WHERE AuditTypeId = CASE WHEN @CheckAuditor = 0
THEN AuditTypeID
ELSE
(SELECT TOP(1) AuditTypeId FROM @AuditTypes at2 WHERE at.AuditTypeId=at2.AuditTypeId AND at2.AuditTypeId IN (5,6,7,8,9))
END
I hope this satisfies your query.. Please tell me if this post is helpful.. 🙂
Cheers
June 22, 2010 at 1:34 pm
For maximum flexibility, create another table with:
CheckAuditor, AuditTypeId
Then INNER JOIN to that table to limit what any CheckAuditor can see.
Something like this:
INSERT INTO CheckAuditor (CheckAuditor, AuditTypeId)
SELECT 0, 1 UNION ALL
SELECT 0, 2 UNION ALL ...
SELECT 0, 9 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 1, 6 UNION ALL ...
SELECT 1, 9
SELECT ...
FROM ...
INNER JOIN CheckAuditor ca ON ca.CheckAuditor = @CheckAuditor AND
ca.AuditTypeID = maintable.AuditTypeId
Scott Pletcher, SQL Server MVP 2008-2010
June 22, 2010 at 3:55 pm
Thanks Scott, yeah I'd thought the best option (not my first thought however...) was to add a column tot he table to handle levels of auditor. I could then handle any number of levels quite simply from this column.
However, the idea of whether I could handle this directly in one procedure was interesting.
Thansk grasshopper for your efforts.
I was able to modify this to use the table (in case they added any more options) as:
DECLARE @CheckAuditor INT
SET @CheckAuditor = 0
SELECT AuditTypeId, AuditType
FROM AuditTypes at
WHERE AuditTypeId = CASE WHEN @CheckAuditor = 0
THEN AuditTypeId
ELSE
(SELECT TOP(1) AuditTypeId FROM AuditTypes at2 WHERE at.AuditTypeId=at2.AuditTypeId AND at2.AuditTypeId IN (5,6,7,8,9))
END
However, I wanted to still include the "(All)" option in the list with the value of 0.
My first thought was
DECLARE @CheckAuditor INT
SET @CheckAuditor = 0
SELECT AuditTypeId, AuditType
FROM AuditTypes at
UNION
SELECT 0, '(All)'
FROM AuditTypes
WHERE AuditTypeId = CASE WHEN @CheckAuditor = 0
THEN AuditTypeId
ELSE
(SELECT TOP(1) AuditTypeId FROM AuditTypes at2 WHERE at.AuditTypeId=at2.AuditTypeId AND at2.AuditTypeId IN (5,6,7,8,9))
END
but this returned a multi-part identifier error
June 22, 2010 at 4:43 pm
FYI, I was talking about a separate table from AuditTypes, basically an AuditTypesControl table.
Scott Pletcher, SQL Server MVP 2008-2010
June 22, 2010 at 5:44 pm
Thanks for the clarification Scott - yes I had picked up on that in your earlier post.
Would it be more efficient to add one column to the existing table and filter records from the table based on this column or to have two tables selected by a UNION?
I was thinking along the lines or
SELECT AuditTypeId, AuditType
FROM AuditTypes
UNION
SELECT 0, '(All)'
FROM AuditTypes
WHERE AuditTypeLevel <= @AuditTypeLevel
In this way, I could have AuditTypeLevel values of 1,2,3 or even more, and display a custom list from the table for each level just in the way you have outlined.
Would there be a noticeable difference in performance and demand, or do the two effectively do the same thing?
June 22, 2010 at 9:33 pm
The IDs are hard-coded in the solution that I have presented.. I agree with Scott that it would be more efficient and easier to maintain if separate table would be created.. Thanks!
Just my personal opinion, I think it is not advisable to add AuditTypeLevel.. If another AuditType would be added, the value for AuditTypeLevel also needs to be determined..
DECLARE @AuditTypes TABLE (AuditTypeId INT, AuditType NVARCHAR(100))
INSERT INTO @AuditTypes
SELECT 1,'Internal' UNION ALL
SELECT 2,'External' UNION ALL
SELECT 3,'Regulator' UNION ALL
SELECT 4,'Special Regulator' UNION ALL
SELECT 5,'Accounting' UNION ALL
SELECT 6,'Health & Safety' UNION ALL
SELECT 7,'Production' UNION ALL
SELECT 8,'Laboratory' UNION ALL
SELECT 9,'Maintenance'
--New table which contains relationship between CheckAuditor (or user) and AuditTypes
--Add foreign key constraint to enforce referential integrity
DECLARE @AuditTypesControl TABLE (CheckAuditor INT, AuditTypeId INT)
INSERT INTO @AuditTypesControl
SELECT 1, 5 UNION ALL
SELECT 1, 6 UNION ALL
SELECT 1, 7 UNION ALL
SELECT 1, 8 UNION ALL
SELECT 1, 9
DECLARE @CheckAuditor INT
SET @CheckAuditor = 1
SELECT 0, '(All)'
UNION ALL
SELECT at.AuditTypeId, at.AuditType
FROM @AuditTypes at
LEFT JOIN @AuditTypesControl atc ON at.AuditTypeId=atc.AuditTypeId
WHERE ISNULL(atc.CheckAuditor,0) = CASE WHEN @CheckAuditor = 0 THEN ISNULL(atc.CheckAuditor,0) ELSE @CheckAuditor END
I'm guessing that this will be used as list of dropdown values to generate report.. 😀
Thanks for appreciating my effort!
June 22, 2010 at 11:29 pm
Yes, used to select records by filtering them, or reporting records by type, and logging a record type based on what types the user can select.
If I modified the table to AuditTypeId int, AuditType nvarchar(100), AuditLevel int
I could then have the table as:
1Internal2
2External2
3Regulator1
4Special Regulator2
5Accounting1
6Health & Safety1
7Production1
8Laboratory1
9Maintenance1
10Environmental3
The user would select the level when they added a new AuditType in the application as with AuditTypeId 10.
So when @AuditLevel = 1 the output is
3Regulator1
5Accounting1
6Health & Safety1
7Production1
8Laboratory1
9Maintenance1
and when 3 it returns the whole list.
Output would need to be based on the table though.
However, is there any way to get the code to work so that when @CheckAuditor = 0 it returns all rows from the table plus the "(All)"
0(All)
1Internal
2External
3Regulator
4Special Regulator
5Accounting
6Health & Safety
7Production
8Laboratory
9Maintenance
10Environmental
I had DECLARE @CheckAuditor INT
SET @CheckAuditor = 0
SELECT AuditTypeId, AuditType
FROM AuditTypes at
UNION
SELECT 0, '(All)'
FROM AuditTypes
WHERE AuditTypeId = CASE WHEN @CheckAuditor = 0
THEN AuditTypeId
ELSE
(SELECT TOP(1) AuditTypeId FROM AuditTypes at2 WHERE at.AuditTypeId=at2.AuditTypeId AND at2.AuditTypeId IN (5,6,7,8,9))
END
but this returns an error.
And if this is possible, is this option more efficient (quicker, less drain) than using a single table as above?
June 24, 2010 at 3:09 am
My apologies for my late reply..
I used your sample data with additional AuditLevel column.. This will include '(All)' if @CheckAuditor = 0..
DECLARE @AuditTypes TABLE (AuditTypeId INT, AuditType NVARCHAR(100), AuditLevel INT)
DECLARE @CheckAuditor INT
SET @CheckAuditor = 0
INSERT INTO @AuditTypes
SELECT 1,'Internal',2 UNION ALL
SELECT 2,'External',2 UNION ALL
SELECT 3,'Regulator',1 UNION ALL
SELECT 4,'Special Regulator',2 UNION ALL
SELECT 5,'Accounting',1 UNION ALL
SELECT 6,'Health & Safety',1 UNION ALL
SELECT 7,'Production',1 UNION ALL
SELECT 8,'Laboratory',1 UNION ALL
SELECT 9,'Maintenance',1 UNION ALL
SELECT 10,'Environmental',3
SELECT 0 AS AuditTypeId, '(All)' AS AuditType WHERE @CheckAuditor=0
UNION ALL
SELECT AuditTypeId, AuditType
FROM @AuditTypes at
WHERE AuditLevel <= CASE WHEN @CheckAuditor=0 THEN AuditLevel ELSE @CheckAuditor END
Does it produced your expected outcome?
Cheers 😀
June 24, 2010 at 4:06 pm
That's exactly what I'm after! Thanks shield_21
I need to do some testing on implementing this as it gives me greater options on the AuditLevel that I don't have in my present solution.
However, I've also been able to come up with this code that means I can replace the current three query's with:
DECLARE @CheckAuditor INT
SET @CheckAuditor = 1
SELECT 0 AS AuditTypeId, '(All)' AS AuditType WHERE @CheckAuditor=0
UNION ALL
SELECT AuditTypeId, AuditType
FROM AuditTypes at
WHERE AuditTypeId = CASE WHEN @CheckAuditor=0 THEN AuditTypeId ELSE 5 END OR
AuditTypeId = CASE WHEN @CheckAuditor=0 THEN AuditTypeId ELSE 6 END OR
AuditTypeId = CASE WHEN @CheckAuditor=0 THEN AuditTypeId ELSE 7 END OR
AuditTypeId = CASE WHEN @CheckAuditor=0 THEN AuditTypeId ELSE 8 END OR
AuditTypeId = CASE WHEN @CheckAuditor=0 THEN AuditTypeId ELSE 9 END OR
AuditTypeId = CASE WHEN @CheckAuditor=0 THEN AuditTypeId ELSE 10 END
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply