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, 2010 at 01:04 PM in Default

avatar image

johnshaddad
43 8 8 13

(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, 2010 at 09:13 PM

avatar image

Squirrel
2.7k 1 4 7

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, 2010 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, 2010 at 02:21 PM

avatar image

David Wimbush
10.2k 30 34 43

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, 2010 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, 2010 at 11:37 PM David Wimbush
  • 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, 2010 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, 2010 at 01:12 PM

avatar image

ThomasRushton ♦♦
39.7k 20 49 52

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, 2010 at 01:21 PM johnshaddad

Random thought: Are there any deletion triggers on the tables being deleted?

Jul 22, 2010 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, 2010 at 01:25 PM johnshaddad

Good for you. :-)

And a big slap on the head for me. Hang on...

Jul 22, 2010 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, 2010 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.

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:

x68

asked: Jul 22, 2010 at 01:04 PM

Seen: 11703 times

Last Updated: Jul 22, 2010 at 01:12 PM

Copyright 2016 Redgate Software. Privacy Policy