Help with rewriting code without cursor

Hello,
Just wondering if anyone can tell me the best way to rewrite the below code
without a cursor.
It's just passing each Id to a stored procedure.
Let me know if you need any more info.
Thanks & go easy on me, I know cursors tend to rile everyone up.
Declare cur_DeleteStuff Cursor Scroll For
Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID)
Open cur_DeleteStuff
Fetch First FROM cur_DeleteStiff into @.ChildTableID
While (@.@.Fetch_Status <> -1)
Begin
exec sp_SMART_ANADeleteFrom @.ChildTableID, 1, 0
If @.@.Error <> 0
BEGIN
ROLLBACK Transaction CDTTransfer
RAISERROR('Something Bad Happened, Updates ROLLED BACK!',1,1)
RETURN
END
Fetch Next FROM cur_Deletestuff into @.ChildTableID
END
Close cur_DeleteStuff
Deallocate cur_DeleteStuff"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> Hello,
> Just wondering if anyone can tell me the best way to rewrite the below
> code
> without a cursor.
> It's just passing each Id to a stored procedure.
> Let me know if you need any more info.
> Thanks & go easy on me, I know cursors tend to rile everyone up.
>
What is the code for the stored procedure: sp_SMART_ANADeleteFrom
If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
based on the ChildTableID
then you should be able to modify the delete to do something like the
following:
DELETE TableName
WHERE ChildTableID IN
(Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID))
One a side note: You should probably not be naming your user defined stored
procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
generally use for SQL Server system stored procedure which are found in the
master database and are available globally throughout the system.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for your help Rick,
Though the naming convention implies it's only deleting a child - it's
actually doing something completely different.
I still need to call the stored procedure for each table ID found.
Thanks for the sp_ info.
Lesley
"Rick Sawtell" wrote:

> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> What is the code for the stored procedure: sp_SMART_ANADeleteFrom
> If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
> based on the ChildTableID
> then you should be able to modify the delete to do something like the
> following:
> DELETE TableName
> WHERE ChildTableID IN
> (Select distinct TableID
> from tbl_DTM
> Where APID IN
> (Select TableID from tbl_DTM where
> supplierID = @.v_FromSupplierID))
>
> One a side note: You should probably not be naming your user defined stor
ed
> procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
> generally use for SQL Server system stored procedure which are found in th
e
> master database and are available globally throughout the system.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Whatever it does, we can't help you find a set-baset solution without you
posting the procedure.
ML|||Sorry, I was thinking I could just use the sp as is. I pasted it below. It
was written a while ago by someone else & is in production now.
Basically it's deleting rows from a table, then deleting the defining row
from another table based on the tableID
I'd welcome any input on how to change this to set based.
That may also address rollback issues I predict I will have.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_SMART_ANADeleteFrom
@.FromTableID INT = 0,
@.OKtoDeleteORIG BIT = 0,
@.DebugMode INT = 0
AS
DECLARE @.TableType CHAR(3)
DECLARE @.AnalyticParentID INT
DECLARE @.FromPhysicalTableName varchar(255)
DECLARE @.strSQL nvarchar(2000)
if @.FromTableID is null
begin
raiserror ('Invalid From Table ID.',1,1)
return
end
--
SELECT @.TableType = MyType,
@.AnalyticParentID = AnalyticParentID,
@.FromPhysicalTableName = PhysicalDataTableName
from
tbl_DataTableMaster where Tableid = @.FromTableID
BEGIN TRANSACTION DELETEfromAnalytics
-- Delete the rows from the Quarterly ANA table
SET @.StrSQL = N'DELETE FROM My_Users.' + @.FromPhysicalTableName +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error deleting rows. Table Deletion did NOT occur!!',1,1)
RETURN
end
--delete row from tbl_DataTableMaster
SET @.strSQL = N'DELETE FROM tbl_DataTableMaster ' +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE data table master Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error Deleting in Data Table Master. Table Deletion did NOT
occur!!',1,1)
RETURN
end
--
COMMIT TRANSACTION DELETEfromAnalytics
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql