| Author |
 Topic  |
|
|
mrleokarthik
Starting Member
16 Posts |
 Posted - 07/19/2006 : 07:59:18
|
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
16769 Posts |
Posted - 07/19/2006 : 08:01:19
|
Nope. This is not allowed
KH
|
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 07/19/2006 : 09:21:22
|
U can create a stored procedure instead
Srinika
|
|
|
|
mrleokarthik
Starting Member
16 Posts |
Posted - 07/19/2006 : 10:09:13
|
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
|
|
|
|
JustDaveN
Starting Member
USA
1 Posts |
Posted - 12/30/2009 : 10:20:20
|
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 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3448 Posts |
Posted - 12/30/2009 : 10:40:26
|
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 |
|
|
| |
Topic |
|
delete statement in function
