Problem with user defined function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with user defined function

Author

Topic

tash
Starting Member

12 Posts

Posted - 2008-08-13 : 12:33:36

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 12:42:31

cant you delete from this table be joining with other two?
delete t1 from Table1 t1
join Table2 t2
on t1.ItemName=t2.ItemName
....

tash
Starting Member

12 Posts

Posted - 2008-08-13 : 13:08:46

quote:
Originally posted by visakh16

cant you delete from this table be joining with other two?
delete t1 from Table1 t1
join Table2 t2
on t1.ItemName=t2.ItemName
....

thank you! it works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources