Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using delete in user defined function 1

Status
Not open for further replies.

bikerboy718

Programmer
Feb 11, 2005
195
US
I never tried this before until now is it possible to use a delete from in a user defined table valed function. I created a memory table in the function and tried to delete from it and found that I could not. I just wanted to know if deletes are not allowed in this manner. Any help would be greatly appreciated.

With Great Power Comes Great Responsibility!!! [afro]

Michael
 
It is something like this but this is a much siplar version of what i am trying to do.


Code:
ALTER FUNCTION [dbo].[udf_MyItems] 
(@plWorkerLoadID BIGINT, @WorkerID INT)
RETURNS @TBL_ReworkItems TABLE
(
	ReportWorkLoadItemID BIGINT
	,OrderItemID BIGINT
)
AS 
BEGIN 
	INSERT @TBL_ReworkItems(ReportWorkLoadItemID,OrderItemID)
	SELECT	DISTINCT ReportWorkLoadItemID, OrderItemID	FROM	ReportWorkLoadItem rwl WITH (nolock)

Delete from @TBL_ReworkItems where ReportWorkLoadItemID = 123
end

With Great Power Comes Great Responsibility!!! [afro]

Michael
 
As you are using a table variable, I don't think you will have acess to it. Instead, try using a temp table or global temp table e.g.
Code:
create table #mytemptable
or
Code:
create table ##myglobaltemptable
They both are available locally, but the global one will be available in any scope.



-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]
 
That is what I was thinking. I am trying to avoid temp tables but if that is the only way to go then that maybe what I have to do. Thanks for your help.

With Great Power Comes Great Responsibility!!! [afro]

Michael
 
What you are trying to do should be possible. You appear to be missing the return statement, but other than that, it should work just fine.

I tested it like this...

Code:
[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] dbo.TestDelete()
Returns @tbl [COLOR=blue]Table[/color](Id BigInt, Data [COLOR=blue]VarChar[/color](20))
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]
	[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @tbl [COLOR=blue]Values[/color](1,[COLOR=red]'Red'[/color])
	[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @tbl [COLOR=blue]Values[/color](2,[COLOR=red]'Green'[/color])
	[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @tbl [COLOR=blue]Values[/color](3,[COLOR=red]'Blue'[/color])

	[COLOR=blue]Delete[/color] [COLOR=blue]From[/color] @tbl [COLOR=blue]Where[/color] Id = 2

	[COLOR=blue]Return[/color]
[COLOR=blue]End[/color]

[COLOR=blue]go[/color]

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] dbo.TestDelete()

This only works because you are creating the table variable within the function (as the return table). You cannot delete from a real table from within a user defined function.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I am trying to make my functions as efficient as possible. If I use a table function I would like to avoid using a temp table.

With Great Power Comes Great Responsibility!!! [afro]

Michael
 
There's not a lot of difference between a temp table and a table variable though (efficiency wise that is). It really depends on each situation, but I wouldn't have thought you would see much performace difference if you switched to temp table.

That said, and judging from George's answer, you may not need to switch.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]
 
Ah.... efficiency is always a worthy goal.

It's not always true that table variables are more efficient than temp tables. In my experience, table variables are usually more efficient, but not always.

Most people don't know this, but there is a really cool 'trick' you can use to boost your performance with table variables. As you know, with regular tables, you can often times get better performance from a query if there is an index on the table. Unfortunately, you cannot create normal indexes on a table variable. But, you can create a table variable with a primary key which is implemented as a clustered index. This is great news! You cannot always create a primary key in a table variable because of the nature of the data you are loading in to it. When you can create a primary key on a table variable, you should at least try it to see if it improves your performance.

For your example, you can create a primary key like this...

Code:
ALTER FUNCTION [dbo].[udf_MyItems]
(@plWorkerLoadID BIGINT, @WorkerID INT)
RETURNS @TBL_ReworkItems TABLE
(
    ReportWorkLoadItemID BIGINT
    ,OrderItemID BIGINT
    [!]Primary Key (ReportWorkLoadItemID, OrderItemID)[/!]
)
AS
BEGIN
    INSERT @TBL_ReworkItems(ReportWorkLoadItemID,OrderItemID)
    SELECT    DISTINCT ReportWorkLoadItemID, OrderItemID    FROM    ReportWorkLoadItem rwl WITH (nolock)

Delete from @TBL_ReworkItems where ReportWorkLoadItemID = 123

    [!]Return[/!]
end

In this case it is safe to add this primary key because you are selecting distinct values in to the table variable.

Now, I'm not saying that this will speed up your queries. What I am saying is that you should try creating a primary key and see if it speeds up your query.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Thanks George. That is good to know.

With Great Power Comes Great Responsibility!!! [afro]

Michael
 
So why not exclude the values from the orignal select?

"NOTHING is more important in a database than integrity." ESquared
 
SQLSister, what I posted was a much simpler version of the actual query and it is very difficult to leave out of the result set.

With Great Power Comes Great Responsibility!!! [afro]

Michael
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top