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.

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.

ASKER CERTIFIED SOLUTION

imran_fast

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.