Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Convert the stored procedure to "Standard SQL Select" Expand / Collapse
Author
Message
Posted Friday, April 11, 2014 9:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 822, Visits: 1,470
I have a very simple stored procedure to be used in Dundas Dashboard:

ALTER proc [dbo].[ddGetCurrentOpenAndClosed]
AS
declare @Open int
declare @Closed int

select @Open = count(1) From Document Where DocType = 1 and DocStatus = 'Active'

Select @Closed = count(1) From Document Where dbo.fnFiscalYear(RetiredDate) = dbo.fnFiscalYear(getdate()) and DocType = 1 and DocStatus = 'Retired'

Select @Open as [Open], @Closed as Closed

fnFiscalYear:
ALTER FUNCTION [dbo].[fnFiscalYear](@AsOf DATETIME)
RETURNS INT
AS
BEGIN

DECLARE @Answer INT

-- Oct 31 is the fiscal year end
-- 20131031 fiscal year is 2013
-- 20131101 fiscal year is 2014
IF ( MONTH(@AsOf) < 11 )
SET @Answer = YEAR(@AsOf)
ELSE
SET @Answer = YEAR(@AsOf) + 1

RETURN @Answer

END

The sp is not in production yet, and it is very time consuming to get it into production, however, I have the choice to use what Dundas refers to "Standard SQL Select" to directly generate the data I need. Here is the error message from Dundas:

The user statement could not be parsed. For security purposes, only statements that translate to standard SELECT SQL are allowed. ORDER BY statements are also disallowed for Virtual Tables.


I guess what it means is I need to remove the use of declare and function.

Can anyone help rewrite the sp?

Thank you very much.

Post #1561021
Posted Friday, April 11, 2014 9:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
SELECT 
[Open] = SUM(CASE WHEN DocStatus = 'Active' THEN 1 ELSE 0 END),
[Closed] = SUM(CASE WHEN DocStatus = 'Retired' THEN 1 ELSE 0 END)
FROM Document
CROSS APPLY (
SELECT
CurrentDateFiscalYear = CASE WHEN MONTH(GETDATE()) < 11 THEN YEAR(GETDATE()) ELSE YEAR(GETDATE())+1,
RetiredDateFiscalYear = CASE WHEN MONTH(RetiredDate) < 11 THEN YEAR(GETDATE()) ELSE YEAR(RetiredDate)+1
) x
WHERE DocType = 1
AND DocStatus IN ('Active','Retired')
AND RetiredDateFiscalYear = CurrentDateFiscalYear

-- you could greatly improve performance by calculating the start and end of the range of RetiredDate
-- for any given value of GETDATE()




“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1561041
Posted Friday, April 11, 2014 10:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 822, Visits: 1,470
thank you so much for your quick reply, other than you forgot the End for the Case, it's working perfectly.
Post #1561059
Posted Friday, April 11, 2014 1:47 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 822, Visits: 1,470
This is driving people crazy!

There is another sp to be converted and this sp contains loop and lots of calculation. The original sp is working fine and logic is very simple, can I have advice from anyone?
Create proc [dbo].[ddGetDevDRROnly]  
AS
Declare @q int --variable for quarter
Declare @balance int
Declare @SVP varchar(20)
Declare @B int --Open Deviations as of Oct 31
Declare @C int --New dev with original TargetClosureDate within current fiscal year
Declare @D int --New dev with original TargetClosureDate beyong current fiscal year
Declare @A int --YTD number of retired dev
Declare @DRR float --A/(B+C+D)

Create table #temp
(
Division varchar(20),
B int,
C int,
D int,
A int,
DRR float
)

--Loop each Division
declare cDivision cursor for
Select Division From ITSDivision

OPEN cDivision
FETCH NEXT FROM cDivision
INTO @SVP
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @B = count(1) FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocStatus = 'Active' and
DocType = 1

SELECT @C = count(1) FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocStatus = 'Active' and
DocType = 1 and
CAST(YEAR(TargetClosureDate) AS int) = dbo.fnFiscalYear(getdate())

SELECT @D = count(1) FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocStatus = 'Active' and
DocType = 1 and
CAST(YEAR(TargetClosureDate) AS int) > dbo.fnFiscalYear(getdate())

SELECT @A = count(1) FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocStatus = 'Retired' and
DocType = 1 and
CAST(YEAR(RetiredDate) AS int) = (dbo.fnFiscalYear(getdate())-1)

IF ((@B + @C + @D)> 0)
BEGIN
Set @DRR = CONVERT(DECIMAL(8,1), 100.0) * @A /(@B + @C + @D)
END
ELSE
BEGIN
Set @DRR = 0
END
Insert into #temp Values(@SVP, @B, @C, @D, @A, @DRR)

FETCH NEXT FROM cDivision
INTO @SVP

END

CLOSE cDivision
DEALLOCATE cDivision

select * from #temp order by DRR desc

Post #1561122
Posted Friday, April 11, 2014 7:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
halifaxdal (4/11/2014)
This is driving people crazy!

There is another sp to be converted and this sp contains loop and lots of calculation. The original sp is working fine and logic is very simple, can I have advice from anyone?
Create proc [dbo].[ddGetDevDRROnly]  
AS
Declare @q int --variable for quarter
Declare @balance int
Declare @SVP varchar(20)
Declare @B int --Open Deviations as of Oct 31
Declare @C int --New dev with original TargetClosureDate within current fiscal year
Declare @D int --New dev with original TargetClosureDate beyong current fiscal year
Declare @A int --YTD number of retired dev
Declare @DRR float --A/(B+C+D)

Create table #temp
(
Division varchar(20),
B int,
C int,
D int,
A int,
DRR float
)

--Loop each Division
declare cDivision cursor for
Select Division From ITSDivision

OPEN cDivision
FETCH NEXT FROM cDivision
INTO @SVP
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @B = count(1) FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocStatus = 'Active' and
DocType = 1

SELECT @C = count(1) FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocStatus = 'Active' and
DocType = 1 and
CAST(YEAR(TargetClosureDate) AS int) = dbo.fnFiscalYear(getdate())

SELECT @D = count(1) FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocStatus = 'Active' and
DocType = 1 and
CAST(YEAR(TargetClosureDate) AS int) > dbo.fnFiscalYear(getdate())

SELECT @A = count(1) FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocStatus = 'Retired' and
DocType = 1 and
CAST(YEAR(RetiredDate) AS int) = (dbo.fnFiscalYear(getdate())-1)

IF ((@B + @C + @D)> 0)
BEGIN
Set @DRR = CONVERT(DECIMAL(8,1), 100.0) * @A /(@B + @C + @D)
END
ELSE
BEGIN
Set @DRR = 0
END
Insert into #temp Values(@SVP, @B, @C, @D, @A, @DRR)

FETCH NEXT FROM cDivision
INTO @SVP

END

CLOSE cDivision
DEALLOCATE cDivision

select * from #temp order by DRR desc



Yes... since the logic is "simple", I strongly recommend that you give it a shot on your own because you're likely the one that's going to have to support it. At least try. The best advice I can give to anyone trying to make such changes is in my signature line below about what to think about.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561166
Posted Friday, April 11, 2014 7:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 822, Visits: 1,470
Jeff Moden (4/11/2014)
halifaxdal (4/11/2014)
This is driving people crazy!

There is another sp to be converted and this sp contains loop and lots of calculation. The original sp is working fine and logic is very simple, can I have advice from anyone?


Yes... since the logic is "simple", I strongly recommend that you give it a shot on your own because you're likely the one that's going to have to support it. At least try. The best advice I can give to anyone trying to make such changes is in my signature line below about what to think about.


Hi Jeff,

Your reply is NOT an advice, it's an encouragement; I like to take your encouragement but I also post the question for advice.

What I would like to know:
1. How to loop the Division in a single Select
2. How to do the calculation for @DRR which is @A /(@B + @C + @D)

It would be appreciated if you or anyone can give me any clue, thank you in advance.
Post #1561168
Posted Friday, April 11, 2014 8:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
Nope... it's absolutely solid "advice" in its purest form. You just don't see it, yet. You're worried about solving the problem all at once and can't see the trees for the forest. You need to peel just one potato at a time. I'll show you what I mean. Let's see if we can walk you into solving this problem.

Look at the code you posted. Following what I said about thinking in columns in my signature line, what do you see in common in each SELECT for each variable being populated in the cursor? The first step would be to write a SINGLE select using the COMMON join criteria that's available in all 4 similar queries.

What should be in the SELECT list itself? That's simple. Remove the COUNT(1) and forget about it for right now. Move all the columns in the WHERE that are not common in all 4 queries up to the SELECT list. Once you've done that, post the code here and we'll show you what's next.

And, don't jump ahead. Remember, we're peeling one potato at a time here so that you learn the steps.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561170
Posted Saturday, April 12, 2014 5:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 822, Visits: 1,470
Thank you Jeff, I appreciate your enlightening, here is what I got so far:
		SELECT	B = Sum(Case When 
DocStatus = 'Active'
Then 1 Else 0 End),
C = Sum(Case When
DocStatus = 'Active' and
CAST(YEAR(TargetClosureDate) AS int) = dbo.fnFiscalYear(getdate())
Then 1 Else 0 End),
D = Sum(Case When
DocStatus = 'Active' and
CAST(YEAR(TargetClosureDate) AS int) > dbo.fnFiscalYear(getdate())
Then 1 Else 0 End),
A = Sum(Case When
DocStatus = 'Retired' and
CAST(YEAR(RetiredDate) AS int) = (dbo.fnFiscalYear(getdate())-1)
Then 1 Else 0 End),
Critical = Sum(Case When
DocStatus = 'Active' and
RiskRating = 4
Then 1 Else 0 End),
High = Sum(Case When
DocStatus = 'Active' and
RiskRating = 3
Then 1 Else 0 End),
Medium = Sum(Case When
DocStatus = 'Active' and
RiskRating = 2
Then 1 Else 0 End),
Low = Sum(Case When
DocStatus = 'Active' and
RiskRating = 1
Then 1 Else 0 End),
TBD = Sum(Case When
DocStatus = 'Active' and
RiskRating = 0
Then 1 Else 0 End),
BCD = Sum(Case When
DocStatus = 'Active'
Then 1 Else 0 End) ----B
+
Sum(Case When
DocStatus = 'Active' and
CAST(YEAR(TargetClosureDate) AS int) = dbo.fnFiscalYear(getdate())
Then 1 Else 0 End) -----C
+
Sum(Case When
DocStatus = 'Active' and
CAST(YEAR(TargetClosureDate) AS int) > dbo.fnFiscalYear(getdate())
Then 1 Else 0 End)

FROM Document d
left outer join ITSDivision i on d.ITSDivisionID = i.ID
Where
i.Division = @SVP and
DocType = 1

It returns all values for a specific @SVP except the DRR which is a percentage DRR = A/(B+C+D), can you help? Thanks.
Post #1561186
Posted Saturday, April 12, 2014 6:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 2,253, Visits: 6,169
halifaxdal (4/11/2014)
I have a very simple stored procedure to be used in Dundas Dashboard:

ALTER proc [dbo].[ddGetCurrentOpenAndClosed]
AS
declare @Open int
declare @Closed int

select @Open = count(1) From Document Where DocType = 1 and DocStatus = 'Active'

Select @Closed = count(1) From Document Where dbo.fnFiscalYear(RetiredDate) = dbo.fnFiscalYear(getdate()) and DocType = 1 and DocStatus = 'Retired'

Select @Open as [Open], @Closed as Closed

fnFiscalYear:
ALTER FUNCTION [dbo].[fnFiscalYear](@AsOf DATETIME)
RETURNS INT
AS
BEGIN

DECLARE @Answer INT

-- Oct 31 is the fiscal year end
-- 20131031 fiscal year is 2013
-- 20131101 fiscal year is 2014
IF ( MONTH(@AsOf) < 11 )
SET @Answer = YEAR(@AsOf)
ELSE
SET @Answer = YEAR(@AsOf) + 1

RETURN @Answer

END

The sp is not in production yet, and it is very time consuming to get it into production, however, I have the choice to use what Dundas refers to "Standard SQL Select" to directly generate the data I need. Here is the error message from Dundas:

The user statement could not be parsed. For security purposes, only statements that translate to standard SELECT SQL are allowed. ORDER BY statements are also disallowed for Virtual Tables.


I guess what it means is I need to remove the use of declare and function.

Can anyone help rewrite the sp?

Thank you very much.



You do not need this function, this can be achieved in a select as this:

;WITH SAMPLE_DATE(XDATE) AS
(
SELECT CONVERT(DATETIME2(0),XDATE,120) AS XDATE
FROM (VALUES
('2007-11-22 00:00:00.000'),('2007-10-03 00:00:00.000')
,('2007-09-21 00:00:00.000'),('2006-11-07 00:00:00.000')
,('2008-04-29 00:00:00.000'),('2006-10-13 00:00:00.000')
,('2008-05-07 00:00:00.000'),('2008-04-05 00:00:00.000')
,('2007-08-05 00:00:00.000'),('2008-06-02 00:00:00.000')
,('2007-12-26 00:00:00.000'),('2007-09-26 00:00:00.000')
,('2008-01-31 00:00:00.000') ) AS X(XDATE)
)

SELECT
SD.XDATE
,YEAR(SD.XDATE) + SIGN(1 + SIGN(MONTH(SD.XDATE) - 11 )) AS FiscalYear
FROM SAMPLE_DATE SD

Post #1561199
Posted Saturday, April 12, 2014 8:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 20,744, Visits: 32,557
There is another way, look at the last Fiscal Year calculation:


WITH SAMPLE_DATE(XDATE) AS
(
SELECT CONVERT(DATETIME2(0),XDATE,120) AS XDATE
FROM (VALUES
('2007-11-22 00:00:00.000'),('2007-10-03 00:00:00.000')
,('2007-09-21 00:00:00.000'),('2006-11-07 00:00:00.000')
,('2008-04-29 00:00:00.000'),('2006-10-13 00:00:00.000')
,('2008-05-07 00:00:00.000'),('2008-04-05 00:00:00.000')
,('2007-08-05 00:00:00.000'),('2008-06-02 00:00:00.000')
,('2007-12-26 00:00:00.000'),('2007-09-26 00:00:00.000')
,('2008-01-31 00:00:00.000') ) AS X(XDATE)
)

SELECT
SD.XDATE
,YEAR(SD.XDATE) + SIGN(1 + SIGN(MONTH(SD.XDATE) - 11 )) AS FiscalYear
,year(dateadd(month,2,SD.XDATE)) As AlsoFiscalYear -- This looks so much cleaner IMHO
FROM SAMPLE_DATE SD;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1561203
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse