Avatar of lizmarc
lizmarc
 asked on

Error 443 in UPDATE statement

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.
Microsoft SQL Server

Avatar of undefined
Last Comment
imran_fast

8/22/2022 - Mon
imran_fast

should be a stored procedure not a function


CREATE procedure SpecifyAsOrdered

     

@rows_affected int output
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


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

END

imran_fast


this should be the stored procedure
CREATE procedure SpecifyAsOrdered

AS
BEGIN
     -- Declare the return variable here
     DECLARE @rows_1          int,
             @rows_2          int,
             @rows_affected   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


     if @rows_1 >= @rows_2
               set @rows_affected = @rows_1
          else
               set @rows_affected = @rows_2;
     
    select @rows_affected
return
END


this is how you call it
exec SpecifyAsOrdered

lizmarc

ASKER
Thanks.  What I'd like to do, though, is call it from a program outside of SQL server as in

int rows_affected = ...
u8sing ADO .NET.  That's why I had it as a function.  Also, how do I actually add this to the database?  When I compile this function, or perhaps the alternative stored procedures listed in the other comments, doesn't that automatically add this to the database?  When I compile this function that I wrote, it does not appear in the list of functions.  Thanks.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
imran_fast

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
imran_fast

now while calling the stored procedure do something like this in the frontend

declare @parameter int
exec SpecifyAsOrdered @parameter output

now the @parameter will contain your value



imran_fast

>>  When I compile this function that I wrote, it does not appear in the list of functions.  Thanks.
You cannot use @@rowcount inside function thats why i suggested a stored procedure

Open query analyzer

Use Yourdatabase Name
Put sp creation code here
then press F5

>>comments, doesn't that automatically add this to the database?
In Enterprise manager you have to click on function in the left panel then refresh.