February 28, 2014 at 11:13 am
Hello,
I have a stored procedure:
Alter proc ddGetDevStatsByUnitStateAndYear (@Unit varchar(20), @State varchar(10), @Year int)
AS
select Unit = isnull(i.Division, 'TBD'), DocStatus, COUNT(1) as Total From Document d
left outer join ITSDivision i on i.ID = d.ITSDivisionID
Where doctype = 1 and
i.Division = Case @Unit When 'All' Then i.Division Else @Unit End and
DocStatus = Case @State When 'All' Then DocStatus When 'Open' Then 'Active' When 'Closed' Then 'Retired' End and
@Year = Case @State When 'Open' Then CAST(YEAR(DateFirstIssued) AS int) When 'Closed' Then CAST(YEAR(RetiredDate) AS int) Else @Year End
group by i.Division, DocStatus
order by Division
I have a question on @Year, the @Year will work this way:
If @Year <> 0 Then depending on DocStatus:
1. If DocStatus is Open, then @Year = CAST(YEAR(DateFirstIssued) AS int)
2. If DocStatus is Closed, then @Year = CAST(YEAR(RetiredDate) AS int)
If @Year = 0 Then no any restriction on this.
How do I write this query?
Thank you.
February 28, 2014 at 11:18 am
Will this works?
((@Year = (Case @State When 'Open' Then CAST(YEAR(DateFirstIssued) AS int) When 'Closed' Then CAST(YEAR(RetiredDate) AS int) Else @Year End ) and @Year > 0) or
(@Year = 0 ))
February 28, 2014 at 11:40 am
Personally, I like the logic clear so if I glance at this in a year, or someone else does, it makes sense. I'd do this:
DECLARE @year INT
, @DateFirstIssued DATETIME
, @RetiredDate DATETIME
SELECT @year = CASE WHEN @year = 0 THEN 0
WHEN @state = 'Open'
THEN CAST(YEAR(@DateFirstIssued) AS INT)
WHEN @state = 'Closed'
THEN CAST(YEAR(@RetiredDate) AS INT)
ELSE @year
END
I would also suggest you indent and break lines to make this clear. When things are not formatted well, it's easy to misread logic.
February 28, 2014 at 11:45 am
Steve Jones - SSC Editor (2/28/2014)
Personally, I like the logic clear so if I glance at this in a year, or someone else does, it makes sense. I'd do this:
DECLARE @year INT
, @DateFirstIssued DATETIME
, @RetiredDate DATETIME
SELECT @year = CASE WHEN @year = 0 THEN 0
WHEN @state = 'Open'
THEN CAST(YEAR(@DateFirstIssued) AS INT)
WHEN @state = 'Closed'
THEN CAST(YEAR(@RetiredDate) AS INT)
ELSE @year
END
I would also suggest you indent and break lines to make this clear. When things are not formatted well, it's easy to misread logic.
Thanks Steve, but @DateFirstIssued and @RetiredDate are not parameters, they are two different fields in table Document
February 28, 2014 at 12:17 pm
halifaxdal (2/28/2014)
Steve Jones - SSC Editor (2/28/2014)
Personally, I like the logic clear so if I glance at this in a year, or someone else does, it makes sense. I'd do this:
DECLARE @year INT
, @DateFirstIssued DATETIME
, @RetiredDate DATETIME
SELECT @year = CASE WHEN @year = 0 THEN 0
WHEN @state = 'Open'
THEN CAST(YEAR(@DateFirstIssued) AS INT)
WHEN @state = 'Closed'
THEN CAST(YEAR(@RetiredDate) AS INT)
ELSE @year
END
I would also suggest you indent and break lines to make this clear. When things are not formatted well, it's easy to misread logic.
Thanks Steve, but @DateFirstIssued and @RetiredDate are not parameters, they are two different fields in table Document
Just
SELECT @year = CASE WHEN @year = 0 THEN 0
WHEN @state = 'Open'
THEN CAST(YEAR(DateFirstIssued) AS INT)
WHEN @state = 'Closed'
THEN CAST(YEAR(RetiredDate) AS INT)
ELSE @year
END
FROM Document
February 28, 2014 at 12:34 pm
Thanks for reply, maybe I didn't make it clearer, I decided to go this way:
ALTER PROC ddGetDevStatsByUnitStateAndYear (@Unit varchar(20), @State varchar(10), @Year int)
AS
IF @Year = 0
BEGIN
SELECT Unit = isnull(i.Division, 'TBD'), DocStatus, COUNT(1) as Total From Document d
LEFT OUTER JOIN ITSDivision i on i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END AND
DocStatus = CASE @State WHEN 'All' THEN DocStatus WHEN 'Open' THEN 'Active' WHEN 'Closed' THEN 'Retired' END
GROUP BY i.Division, DocStatus
ORDER BY COUNT(1) Desc, Division
END
ELSE
BEGIN
SELECT Unit = isnull(i.Division, 'TBD'), DocStatus, COUNT(1) as Total From Document d
LEFT OUTER JOIN ITSDivision i on i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END AND
DocStatus = CASE @State WHEN 'All' THEN DocStatus WHEN 'Open' THEN 'Active' WHEN 'Closed' THEN 'Retired' END AND
@Year = CASE @State WHEN 'Open' THEN CAST(YEAR(DateFirstIssued) AS int) WHEN 'Closed' THEN CAST(YEAR(RetiredDate) AS int) ELSE @Year END
GROUP BY i.Division, DocStatus
ORDER BY COUNT(1) Desc, Division
END
February 28, 2014 at 1:33 pm
halifaxdal (2/28/2014)
Thanks for reply, maybe I didn't make it clearer, I decided to go this way:
ALTER PROC ddGetDevStatsByUnitStateAndYear (@Unit varchar(20), @State varchar(10), @Year int)
AS
IF @Year = 0
BEGIN
SELECT Unit = isnull(i.Division, 'TBD'), DocStatus, COUNT(1) as Total From Document d
LEFT OUTER JOIN ITSDivision i on i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END AND
DocStatus = CASE @State WHEN 'All' THEN DocStatus WHEN 'Open' THEN 'Active' WHEN 'Closed' THEN 'Retired' END
GROUP BY i.Division, DocStatus
ORDER BY COUNT(1) Desc, Division
END
ELSE
BEGIN
SELECT Unit = isnull(i.Division, 'TBD'), DocStatus, COUNT(1) as Total From Document d
LEFT OUTER JOIN ITSDivision i on i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END AND
DocStatus = CASE @State WHEN 'All' THEN DocStatus WHEN 'Open' THEN 'Active' WHEN 'Closed' THEN 'Retired' END AND
@Year = CASE @State WHEN 'Open' THEN CAST(YEAR(DateFirstIssued) AS int) WHEN 'Closed' THEN CAST(YEAR(RetiredDate) AS int) ELSE @Year END
GROUP BY i.Division, DocStatus
ORDER BY COUNT(1) Desc, Division
END
You might want to consider breaking this into separate procs for each "branch" of your if condition. Check out this article from Gail where she explains this exact type of query and the performance issues you can run into.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 1:39 pm
Hello, I finally end up with the following code which is working as expected:
ALTER PROC ddGetDevStatsByUnitStateAndYear (@Unit varchar(20), @State varchar(10), @Year int)
AS
IF @State = 'All'
BEGIN
IF @Year = 0
BEGIN
SELECT Unit = isnull(i.Division, 'TBD'), DocStatus, COUNT(1) as Total From Document d
LEFT OUTER JOIN ITSDivision i on i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END
GROUP BY i.Division, DocStatus
ORDER BY COUNT(1) Desc, Division
END
ELSE
BEGIN
GRANT CREATE TABLE TO AOR_User
if OBJECT_ID('DevStats', 'U') IS NOT NULL
drop table DevStats
CREATE TABLE DevStats
(
Unit varchar(20),
DocStatus varchar(10),
Total int
)
GRANT SELECT ON OBJECT::DevStats TO AOR_User
INSERT INTO DevStats
SELECT isnull(i.Division, 'TBD'), DocStatus, COUNT(1)
FROM Document d
LEFT OUTER JOIN ITSDivision i ON i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END AND
@Year = CAST(YEAR(DateFirstIssued) AS int)
group by i.Division, DocStatus
order by Division
INSERT INTO DevStats
SELECT isnull(i.Division, 'TBD'), DocStatus, COUNT(1)
From Document d
LEFT OUTER JOIN ITSDivision i on i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END AND
@Year = CAST(YEAR(RetiredDate) AS int)
group by i.Division, DocStatus
order by Division
SELECT * FROM DevStats
Order By Unit, Total Desc
END
END
ELSE
BEGIN
IF @Year = 0
BEGIN
SELECT Unit = isnull(i.Division, 'TBD'), DocStatus, COUNT(1) as Total From Document d
LEFT OUTER JOIN ITSDivision i on i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END AND
DocStatus = CASE @State WHEN 'Open' THEN 'Active' WHEN 'Closed' THEN 'Retired' ELSE DocStatus END
GROUP BY i.Division, DocStatus
ORDER BY Division, COUNT(1) Desc
END
ELSE
BEGIN
SELECT Unit = isnull(i.Division, 'TBD'), DocStatus, COUNT(1) as Total From Document d
LEFT OUTER JOIN ITSDivision i on i.ID = d.ITSDivisionID
WHERE doctype = 1 AND
i.Division = CASE @Unit WHEN 'All' THEN i.Division ELSE @Unit END AND
DocStatus = CASE @State WHEN 'Open' THEN 'Active' WHEN 'Closed' THEN 'Retired' ELSE DocStatus END AND
@Year = CASE @State WHEN 'Open' THEN CAST(YEAR(DateFirstIssued) AS int) WHEN 'Closed' THEN CAST(YEAR(RetiredDate) AS int) ELSE @Year END
GROUP BY i.Division, DocStatus
ORDER BY Division, COUNT(1) Desc
END
END
Please note there are codes:
GRANT CREATE TABLE TO AOR_User
if OBJECT_ID('DevStats', 'U') IS NOT NULL
drop table DevStats
CREATE TABLE DevStats
(
Unit varchar(20),
DocStatus varchar(10),
Total int
)
GRANT SELECT ON OBJECT::DevStats TO AOR_User
There is issue here, although the stored procedure has no problem if executed independently in SSMS, however, the sp will be executed as a special service account in a third party environment, the account is a member of role AOR_User which has execute privilege on sp and fn and select on table but no table creation privilege!
That being said, the sp fails in the third party environment.
What would be the best work around for this restriction?
Thank you.
February 28, 2014 at 2:03 pm
Bingo!
Temp table doesn't have that restriction! All I need to do is to create temp table instead of a static table
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply