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 @AnswerINT

-- 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.