August 13, 2008 at 10:54 am
Hello, I have 3 tables with rows, which I want to delete from another table. I want to delete all rows from this table using first one, then second one and then third one table step by step
I want to use a function, smth like this:
create function DeleteRows (@ItemName varchar(100))
returns void as
begin
delete from Table1 where ItemName=@ItemName
end
Then I can call this function in a loop using @ItemName from one of 3 tables.
the problem here, as I understood, is that I can't use VOID as return type in Transact-SQL..I tried to change syntax, but still got errors..
Maybe I can use stored procedure, like this:
create stored procedure DeleteRows (@ItemName varchar(100))
as
begin
delete from Table1 where ItemName=@ItemName
end
[/c0de]
BUT I can't execute it in loop, is it because I must substitute certain string here, like 'AUSTRALIA'??
here is the code:
declare @counter int;
declare @name varchar(100);
set @counter=1
while counter<=(select count(ItemID) from Table2)
begin
select @name=(select ItemName from Table2 where ItemID=@counter)
execute DeleteRows(@name) <--------------HERE!
@counter=@counter+1
end
Is there any way to call this procedure in such loop, or maybe I need another solution.
Help me, please
August 13, 2008 at 11:07 am
Looks like it should work. What problem are you having?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 13, 2008 at 11:12 am
the problem with UDF is that I can't use VOID as a return type (is it correct for T-SQL??), problem with stored procedure is that I can't call it in a loop (call with a variable)
As I understood, I must call stored procedure with exact string, like 'AUSTRALIA', but not with initialized variable, like @name (in my example)
Is it correct??
actually I've solved this problem using delete+join statements...but still I'm interested in questions described above 🙂
August 13, 2008 at 2:51 pm
natalia.rodchenko (8/13/2008)
problem with stored procedure is that I can't call it in a loop (call with a variable)As I understood, I must call stored procedure with exact string, like 'AUSTRALIA', but not with initialized variable, like @name (in my example)
Is it correct??
No. You can call it in a loop. You can call it with a variable.
It is best to test the things that people tell you (even me, I don't mind).
🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 13, 2008 at 4:12 pm
It is unclear what you attempting to do with names like Table1 and Table2, but why is the solution so complex?
This SQL appears to be a logic equivalent:
delete from Table1
where EXISTS
(select 1
from Table2
where Table2.ItemName = Table1.ItemName
)
SQL = Scarcely Qualifies as a Language
August 14, 2008 at 4:09 am
I can't follow you: You're using a function, but you don't want a result from that function. So use a procedure. Use it like
exec {procedurename} @param1=@var, @param2=@var, etc, etc
Or stick with the function and return the number of deleted rows (for example)
Wilfred
The best things in life are the simple things
August 18, 2008 at 12:01 am
It's not so much that you're not returning a value from a function. You could hard code a return value if you want and it still won't compile. However, the error message will be more meaningful once you get past the syntax errors. The message will politely explain that you can't put a DELETE inside a function. You also cannot put INSERT or UPDATE, or DROP TABLE or anything like that. A function should not make changes to the database or any of the data contained therein. It can SELECT but that's about it.
If you want to have something that deletes from tables, it must be a stored procedure. You can even have the procedure return the number of rows deleted just like a function.
execute @NumRowsDeleted = dbo.DeleteFromTables @IDValue...
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
August 18, 2008 at 2:22 am
Hello Natalia,
the problem with your code is probably in the parenthesis. You could try to call your procedure with parameter like that:
EXECUTE DeleteRows @ItemName=@name
instead of EXECUTE DeleteRows(@name).
As others have remarked, it has to be stored procedure, because a function may not change data.
But your code has several problems inside, not just this one...
- in the WHILE clause, variable @counter is missing the @ sign
- the line which increases counter (@counter=@counter+1) is missing SET keyword
Also, the loop works on assumption that ItemID has consecutive values from 1 up... probably it is IDENTITY column, but even so there could be some gaps - and that would mess up your code. Otherwise, I think this should work.
I'm not sure what led you to this loop solution, in my opinion a normal DELETE query should do the job well... one like Carl Federl posted, or one with JOIN like this:
DELETE T1
FROM Table1 T1
JOIN Table2 T2 ON T2.ItemName=T1.ItemName
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply