Invalid use of side effecting operator in function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Invalid use of side effecting operator in function

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-05 : 10:09:30

I am getting the following error message on delete statement:
Invalid use of side effecting operator within a function.
I have a user defined function would like to delete extra rows after i fill in table variable.



DECLARE @INIT_TaskLog TABLE ( 
taskid int,
PctComplete int,
PctCompleteDate datetime, 
award_amt float,
ProgID int,
QtryearNum int,
MonthNum int,
YearNum int)

INSERT into @INIT_TaskLog (taskid, PctComplete, PctCompleteDate, award_amt, ProgID,QtryearNum,MonthNum,YearNum)
SELECT taskid, PctComplete, PctCompleteDate, award_amt, ProgID,
Case 
When MONTH(PctCompleteDate)=1 Or MONTH(PctCompleteDate)=2 or MONTH(PctCompleteDate)=3 then 1
when MONTH(PctCompleteDate)=4 Or MONTH(PctCompleteDate)=5 or MONTH(PctCompleteDate)=6 Then 2
When MONTH(PctCompleteDate)=9 Or MONTH(PctCompleteDate)=8 or MONTH(PctCompleteDate)=7 then 3
when MONTH(PctCompleteDate)=10 Or MONTH(PctCompleteDate)=11 or MONTH(PctCompleteDate)=12 Then 4
End  as 'QtryearNum', 
month(PctCompleteDate) as 'MonthNum',
year(PctCompleteDate) as 'YearNum'
from TAB_ccsNetTasksLog where ProgID=@ProgID and PctComplete <> 0 order by Updated_dt

delete s
from   (
       select taskid
              ,Pctcompletedate
              ,QtryearNum
              ,YearNum
              ,rn = row_number() over (partition by taskid,QtryearNum,YearNum 
                                  order by Pctcompletedate desc)
       from   @INIT_TaskLog
       ) d
join   @INIT_TaskLog s
       on s.YearNum = d.YearNum
       and s.QtryearNum = d.QtryearNum
       and s.PctCompleteDate = d.PctCompleteDate
       and s.taskid = d.taskid
where  d.rn > 1

Thank you very much for the helpful info.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-05 : 15:06:43

Did you create a function with a delete statement in it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-10-05 : 15:51:14

Replace this piece of code

Case 
When MONTH(PctCompleteDate)=1 Or MONTH(PctCompleteDate)=2 or MONTH(PctCompleteDate)=3 then 1
when MONTH(PctCompleteDate)=4 Or MONTH(PctCompleteDate)=5 or MONTH(PctCompleteDate)=6 Then 2
When MONTH(PctCompleteDate)=9 Or MONTH(PctCompleteDate)=8 or MONTH(PctCompleteDate)=7 then 3
when MONTH(PctCompleteDate)=10 Or MONTH(PctCompleteDate)=11 or MONTH(PctCompleteDate)=12 Then 4
End  as 'QtryearNum'

with this more elegant

DATEPART(QUARTER, PctCompleteDate) AS QtrYearNum

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-10-05 : 16:00:10

Or... Why not remove the DELETE at all?

DECLARE @INIT_TaskLog TABLE
	( 
		TaskID INT,
		PctComplete INT,
		PctCompleteDate DATETIME, 
		Award_Amt FLOAT,
		ProgID INT,
		QtrYearNum TINYINT,
		MonthNum TINYINT,
		YearNum SMALLINT
	)

INSERT	@INIT_TaskLog
	(
		TaskID,
		PctComplete,
		PctCompleteDate,
		Award_Amt,
		ProgID,
		QtrYearNum,
		MonthNum,
		YearNum
	)
SELECT	TaskID,
	PctComplete,
	PctCompleteDate,
	Award_Amt,
	ProgID,
	QtrYearNum, 
	MonthNum,
	YearNum
FROM	(
		SELECT	TaskID,
			PctComplete,
			PctCompleteDate,
			Award_Amt,
			ProgID,
			DATEPART(QUARTER, PctCompleteDate) AS QtrYearNum, 
			DATEPART(MONTH, PctCompleteDate) AS MonthNum,
			DATEPART(YEAR, PctCompleteDate) AS YearNum,
			ROW_NUMBER() OVER (PARTITION BY DATEDIFF(QUARTER, 0, PctCompleteDate), TaskID ORDER BY PctCompleteDate DESC) AS RecID
		FROM	dbo.TAB_ccsNetTasksLog
		WHERE	ProgID = @ProgID
			AND PctComplete <> 0
	) AS d
WHERE	RecID = 1

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources