Cannot Return Data Error 2

[bakershawnm]

Using MS Sql Server 2005 I have the following funcion:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION DeltaDays
(
-- Add the parameters for the function here
@StrtDate datetime,
@EndDate datetime
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int
DECLARE @dayofweek as INT
-- Add the T-SQL statements to compute the return value here
WHILE DATEDIFF(day, @StrtDate, @EndDate) > 0
BEGIN
SET @dayofweek = DATEPART(WEEKDAY, @StrtDate)
IF @dayofweek > 1 AND @dayofweek < 7
BEGIN
SELECT DOWNDAYS_I
FROM KMM.dbo.DD010000
WHERE DOWNDAYS_I = @StrtDate
-- IF DOWNDAYS_I IS NULL
-- BEGIN
-- set @Result = @Result + 1
-- END
END
SET @StrtDate = DATEADD(day, 1, @StrtDate)
END
-- Return the result of the function
RETURN @Result

END
GO

I get the following error:

Msg 444, Level 16, State 2, Procedure DeltaDays, Line 25
Select statements included within a function cannot return data to a client.

From everything I have found this error usually indicates returning more than one record. However with the WHERE clause I should get either 1 of 2 results; either a null or a single date.

If this error is not because of the SELECT statement then what is it on and what do I need to do to fix this?

Thanks

 

[gmmastros]

Your problem is here:

[tt][blue]
SET @dayofweek = DATEPART(WEEKDAY, @StrtDate)
IF @dayofweek > 1 AND @dayofweek < 7
BEGIN
[!]SELECT DOWNDAYS_I
FROM KMM.dbo.DD010000
WHERE DOWNDAYS_I = @StrtDate[/!]
-- IF DOWNDAYS_I IS NULL
-- BEGIN
-- set @Result = @Result + 1
-- END
END
SET @StrtDate = DATEADD(day, 1, @StrtDate)
[/blue][/tt]

This is a function, and you have a regular ole' select statement in it. It's hard to tell what this is supposed to do, but maybe something like this:

        SET @dayofweek = DATEPART(WEEKDAY, @StrtDate)
        IF  @dayofweek > 1 AND @dayofweek < 7
        BEGIN
            If Not Exists(SELECT DOWNDAYS_I
                          FROM   KMM.dbo.DD010000
                          WHERE DOWNDAYS_I = @StrtDate)
              set @Result = @Result + 1
--            IF DOWNDAYS_I IS NULL
--            BEGIN
--                set @Result = @Result + 1
--            END
        END
        SET @StrtDate = DATEADD(day, 1, @StrtDate)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

1. How you're calling this function from the client?

2. Your function code is not correct.
Change it to

declare @DownDays_I INT
 SELECT @DOWNDAYS_I = DownDays_I            FROM   KMM.dbo.DD010000            WHERE DOWNDAYS_I = @StrtDate

IF @DOWNDAYS_I IS NULL
            BEGIN
               set @Result = @Result + 1            END        

END

Also, where did you set @Result to something initially?

 

[bakershawnm]

Thanks both of you for the input.

I went with markros suggestion since it was the easiest to add.

you can both have a star since both would work well.

I guess I can go home now since I have learned something new today.

 

[bborissov]

It is easier, but is somehow slower that George's suggestion.
Just because IF EXISTS() check for the FIRST existence of the records and then stops execution.
Also if there is NO data and @DOWNDAYS_I already has some value, the construction;

SELECT @SomeVariable = SomeField FROM SomeTable WHERE ...

will not change the @SomeVariable value to NULL.
You must use:

[code]
SET @SomeVariable = (SELECT SomeField FROM SomeTable WHERE ...)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.