x

HOW TO SOLVE: Only functions and some extended stored procedures can be executed from within a function.

I have the following FUNCTION and PROCEDURE. When I call the function on a normal SELECT statement (ie. "SELECT dbo.CLIENT_DELETE('100');") it returns the following error:

'Only functions and some extended stored procedures can be executed from within a function.'

Here is my FUNCTION and PROCEDURE codes:

USE [final]
GO
/****** Object:  StoredProcedure [dbo].[CLIENT_DELETE_PROC]    Script Date: 07/22/2010 15:23:35 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CLIENT_DELETE_PROC]
    @clientid_passed int
AS
BEGIN

  DECLARE @a int;
  DECLARE cur1 CURSOR FOR SELECT con_contacts.contactid FROM con_contacts,cli_clients WHERE cli_clients.clientid=@clientid_passed AND con_contacts.contactid=cli_clients.contactid;

OPEN cur1;

    FETCH NEXT FROM cur1 
    INTO @a

    -- LOOP THROUGH PRODUCT ITEMS 
    WHILE @@FETCH_STATUS = 0
    BEGIN

       DELETE FROM con_contacts WHERE contactid=@a;

    FETCH NEXT FROM cur1
    INTO @a

    END
CLOSE cur1;

DEALLOCATE products_cursor

DELETE FROM users WHERE clientid=@clientid_passed;
DELETE FROM cli_clients WHERE clientid=@clientid_passed;

END


create function CLIENT_DELETE(@clientid_passed int)
returns INT
as
begin

EXEC CLIENT_DELETE_PROC @clientid_passed

return 0

end
SOS!
more ▼

asked Jul 22 '10 at 01:04 PM in Default

johnshaddad gravatar image

johnshaddad
43 8 8 10

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

I am not C/C++/PHP coder. But can't PHP call a stored procedure ?

You can write a stored procedure in SQL Server that perform what you want here.

Anyway your current CLIENT_DELETE_PROC is already doing it. Here i make some changes to remove the cursor loop which is not required at all

CREATE PROCEDURE [dbo].[CLIENT_DELETE_PROC]
    @clientid_passed int
AS
BEGIN
    DELETE d
    FROM   con_contacts d
    WHERE  d.clientid = @clientid_passed

    DELETE d
    FROM   users d
    WHERE  d.clientid = @clientid_passed;  

    DELETE d
    FROM   cli_clients d
    WHERE  d.clientid = @clientid_passed;
END
And in your PHP call the stored procedure CLIENT_DELETE_PROC to delete the record.
more ▼

answered Jul 22 '10 at 09:13 PM

Squirrel gravatar image

Squirrel
1.5k 1 2 4

I just tried this, and it worked fine as a procedure! I just need to change my QUERY STATEMENTS in PHP to be EXEC for procedures instead of SELECT for functions.

If I run to any problems with converting any of the functions, I will buzz you again xD Thanks alot!
Jul 22 '10 at 10:08 PM johnshaddad
(comments are locked)
10|1200 characters needed characters left
A user defined function can't update the database, only variables. Check out CREATE FUNCTION in Books Online.
more ▼

answered Jul 22 '10 at 02:21 PM

David Wimbush gravatar image

David Wimbush
4.7k 28 29 31

I am not using a function to update. I am using the function to CALL a procedure. Read my post again please to see how I am defining BOTH procedure and function.

I am already aware that I can't do UPDATE/DELETE/INSERT from Functions in MSSQL.
Jul 22 '10 at 02:23 PM johnshaddad
But calling an update stored proc is still doing updates and when the Query Optimizer builds a plan it sees that and rejects it. I think you're misunderstanding what SQL functions are about. They aren't like functions in a typical programming language. They're a way to encapsulate a specific bit of logic for a SELECT query, like converting a money amount to a different currency by looking up the current rate and multiplying. Don't create a function just to call your stored proc, just call the stored proc.
Jul 22 '10 at 11:37 PM David Wimbush
+1 but I wish you had put your comments in the answer section as they are the root reasons for his dilemma. Hopefully the OP will mark yours as correct.
Jul 23 '10 at 05:46 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

Ignore this answer - it's wrong (apart from the 3rd attempt). However, there's some useful discussion in the comments area, which is why I haven't deleted it.

Can't be done. At least not like that.

Try replacing the cursor and its associated code with the following:

DELETE FROM con_contacts 
WHERE contactid IN 
      (SELECT con_contacts.contactid 
       FROM con_contacts,cli_clients 
       WHERE cli_clients.clientid=@clientid_passed 
         AND con_contacts.contactid=cli_clients.contactid
      )

--edit--

I'm sorry, I've been going about this the wrong way. The error message is the answer - "only some extended stored procedures and functions..."

2nd attempt at the answer:

Rip out the reference to the stored procedure, and put those three deletion statements into the function.

3rd attempt

OK, so I'm wrong there too. You can't alter data within a function. Apologies for the run-around - I blame it on lack of sleep.
more ▼

answered Jul 22 '10 at 01:12 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 13 20 44

I tried replacing the cursor with the code given, but the same error is still appearing :S I even tried it without that whole part, only the two delete statements, and it still showed the same error (even after restarting the server)

Here is the new procedure code as suggested:

USE [final] GO /****** Object: StoredProcedure [dbo].[CLIENT_DELETE_PROC] Script Date: 07/22/2010 16:11:05 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[CLIENT_DELETE_PROC] @clientid_passed int AS BEGIN

DELETE FROM con_contacts WHERE contactid IN (SELECT con_contacts.contactid FROM con_contacts,cli_clients WHERE cli_clients.clientid=@clientid_passed AND con_contacts.contactid=cli_clients.contactid)

DELETE FROM users WHERE clientid=@clientid_passed; DELETE FROM cli_clients WHERE clientid=@clientid_passed;

END
Jul 22 '10 at 01:21 PM johnshaddad
Random thought: Are there any deletion triggers on the tables being deleted?
Jul 22 '10 at 01:23 PM ThomasRushton ♦
No, there are no triggers for those tables. I don't even have any trigger in the whole database so far.
Jul 22 '10 at 01:25 PM johnshaddad

Good for you. :-)

And a big slap on the head for me. Hang on...
Jul 22 '10 at 01:28 PM ThomasRushton ♦

I can't put DELETE statements inside a MSSQL Function :S

I tried per your request, and here is what I got:

"Invalid use of a side-effecting operator 'DELETE' within a function."
Jul 22 '10 at 02:31 PM johnshaddad
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x55

asked: Jul 22 '10 at 01:04 PM

Seen: 9990 times

Last Updated: Jul 22 '10 at 01:12 PM