troubleshooting Question

Error 443 in UPDATE statement

Avatar of lizmarc
lizmarc asked on
Microsoft SQL Server
6 Comments1 Solution2107 ViewsLast Modified:
I'm writing my first SQL Server 2005 function.  I've only done Oracle before.  I thought it was straightforward, but apparently I'm doing something silly and can't work out what it is.  Also, once the function compiles successfully, how do you actually add it to the database in management studio?  I create a new scalar-valued function, it compiles ok (although it does not execute properly, see below) but doesn't seem to be added to the database.

Anyway, the function is as follows.  It's simple and not really worthy of production but it's a learning exercise.

CREATE FUNCTION SpecifyAsOrdered
(
      
)
RETURNS int
AS
BEGIN
      -- Declare the return variable here
      DECLARE @rows_1            int
      DECLARE @rows_2            int

      update dbo.books
            set status_id = 3
            where status_id = 2

      set @rows_1 = @@ROWCOUNT
      update dbo.orders
            set status_id = 3
            where status_id = 2

      set @rows_2 = @@ROWCOUNT

      DECLARE @rows_affected            int
      if @rows_1 >= @rows_2
      begin
            set @rows_affected = @rows_1
      end
      else
      begin
            set @rows_affected = @rows_2;
      end


      RETURN @rows_affected

END

The error I get when trying to execute this is:

Msg 443, Level 16, State 15, Procedure SpecifyAsOrdered, Line 12
Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.
Msg 443, Level 16, State 15, Procedure SpecifyAsOrdered, Line 17
Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.


Line 12 is the first update statement.  It's the line that says update dbo.books

Any ideas?  I basically followed sample code although I'm certainly not used to the lack of ; after each statement.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros