SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 delete statement in function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrleokarthik
Starting Member

16 Posts

Posted - 07/19/2006 :  07:59:18  Show Profile  Reply with Quote
Is it possible to create a function that deletes records from a table ?

CREATE FUNCTION F_TSImported_Delete()
returns int
as
Begin
delete from ts_imported
return 0
end
GO


This throws error like this:
Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 07/19/2006 :  08:01:19  Show Profile  Reply with Quote
Nope. This is not allowed


KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 07/19/2006 :  08:06:33  Show Profile  Reply with Quote
also refer to here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_460j.asp


KH

Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 07/19/2006 :  09:21:22  Show Profile  Reply with Quote
U can create a stored procedure instead

Srinika
Go to Top of Page

mrleokarthik
Starting Member

16 Posts

Posted - 07/19/2006 :  10:09:13  Show Profile  Reply with Quote
quote:
Originally posted by mrleokarthik

Is it possible to create a function that deletes records from a table ?

CREATE FUNCTION F_TSImported_Delete()
returns int
as
Begin
delete from ts_imported
return 0
end
GO


This throws error like this:
Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.



Thank you for your reply.

Yes, i can use Stored procedure
Go to Top of Page

JustDaveN
Starting Member

USA
1 Posts

Posted - 12/30/2009 :  10:20:20  Show Profile  Reply with Quote
Just as an FYI, you can call a stored procedure from within your function. If you need to keep what you are doing as a function, you can take the external table changes and put it in a stored proc, then call it.

A lot of folks would ask why you would want to do this. For my specific example, I had written a table function that was used in several stored procedures. It worked well as the function ended up changing and rather than having to change this block of code in 10 or more procs, I only had to change it once. Later, a business requirement was added to log some intermediate calculations that were used in determining the data set returned. I added a procedure call to log the value in an external table.

HTH
David
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/30/2009 :  10:40:26  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
quote:
Originally posted by JustDaveN

Just as an FYI, you can call a stored procedure from within your function. If you need to keep what you are doing as a function, you can take the external table changes and put it in a stored proc, then call it.

A lot of folks would ask why you would want to do this. For my specific example, I had written a table function that was used in several stored procedures. It worked well as the function ended up changing and rather than having to change this block of code in 10 or more procs, I only had to change it once. Later, a business requirement was added to log some intermediate calculations that were used in determining the data set returned. I added a procedure call to log the value in an external table.

HTH
David



Eh -- no you cant!

At least you can't in 2005 or in 2000. I don't know about 2008. Functions can't change data The procedures and other external objects you can call from inside a function are very limited.

Example

CREATE PROCEDURE cg_write_val
AS BEGIN
	SELECT 'a' AS [Foo] INTO _test
END
GO

CREATE FUNCTION myFunc
	(@foo INT) RETURNS INT
AS BEGIN
	EXEC cg_write_val
	SET @foo = -1
	RETURN @foo
END
GO

SELECT dbo.myFunc(1)

Give the error on run:

Msg 557, Level 16, State 2, Line 2
Only functions and extended stored procedures can be executed from within a function.



EDIT : HOLY THREAD RESURRECTION BATMAN!
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 12/30/2009 10:49:36
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000