Best practice...

Hi,
I was an oracle programmer recently moved to MSSql. So I'm trying to learn
the right way. Oracle was very cursor oriented & MSSQl seems to seriously
discourage using them at all wherever possible.
In this situation here's what I roughly written, with cursors - it'll give
you the direction I am going at least. I want to re-write properly without
cursors.
Declare cur_DeleteChild Cursor Scroll For
Select TableID
from tbl_TableMaster
Where ParentID in
(Select TableID from tbl_TableMaster where
supplierID = @.v_FromSupplierID)
Open cur_DeleteChild
Fetch First FROM cur_DeleteChild into @.ChildTableID
While (@.@.Fetch_Status <> -1)
Begin
exec sp_DeleteChild @.ChildTableID
END
Fetch Next FROM cur_DeleteChild into @.ChildTableID
Close cur_DeleteChild
Deallocate cur_DeleteChild
These are not large tables, nor will this sp be run often.
Rather than the subquery, I've read I should use temp table if necessary,
but in most cases I can avoid using them at all.
(I know temp tables do not perform the same purpose as cursors)
Thanks for any suggestions.I always use cursors and have not been sacked yet
depends on what you want to have happen in the cursor and the volumes of
data, they can be just as useful as in oracle.
Having said that I also use temp tables too.
"Lesley" wrote:

> Hi,
> I was an oracle programmer recently moved to MSSql. So I'm trying to lear
n
> the right way. Oracle was very cursor oriented & MSSQl seems to seriously
> discourage using them at all wherever possible.
> In this situation here's what I roughly written, with cursors - it'll give
> you the direction I am going at least. I want to re-write properly witho
ut
> cursors.
> Declare cur_DeleteChild Cursor Scroll For
> Select TableID
> from tbl_TableMaster
> Where ParentID in
> (Select TableID from tbl_TableMaster where
> supplierID = @.v_FromSupplierID)
> Open cur_DeleteChild
> Fetch First FROM cur_DeleteChild into @.ChildTableID
> While (@.@.Fetch_Status <> -1)
> Begin
> exec sp_DeleteChild @.ChildTableID
> END
> Fetch Next FROM cur_DeleteChild into @.ChildTableID
> Close cur_DeleteChild
> Deallocate cur_DeleteChild
> These are not large tables, nor will this sp be run often.
> Rather than the subquery, I've read I should use temp table if necessary,
> but in most cases I can avoid using them at all.
> (I know temp tables do not perform the same purpose as cursors)
> Thanks for any suggestions.|||Looks like a cascaded DELETE to me. Have you considered using the ON DELETE
CASCADE option? Otherwise you can use a DELETE trigger.
If your table has a self-referencing foreign key and represents a hierarchy
of some unknown depth then you'll possibly want to use a Recursive trigger.
However, there are other data models for this kind of hierarchy that may
serve you better. Google for "Materialized Path" or "Nested Sets" if you
aren't familiar with them.
David Portas
SQL Server MVP
--
"Lesley" wrote:

> Hi,
> I was an oracle programmer recently moved to MSSql. So I'm trying to lear
n
> the right way. Oracle was very cursor oriented & MSSQl seems to seriously
> discourage using them at all wherever possible.
> In this situation here's what I roughly written, with cursors - it'll give
> you the direction I am going at least. I want to re-write properly witho
ut
> cursors.
> Declare cur_DeleteChild Cursor Scroll For
> Select TableID
> from tbl_TableMaster
> Where ParentID in
> (Select TableID from tbl_TableMaster where
> supplierID = @.v_FromSupplierID)
> Open cur_DeleteChild
> Fetch First FROM cur_DeleteChild into @.ChildTableID
> While (@.@.Fetch_Status <> -1)
> Begin
> exec sp_DeleteChild @.ChildTableID
> END
> Fetch Next FROM cur_DeleteChild into @.ChildTableID
> Close cur_DeleteChild
> Deallocate cur_DeleteChild
> These are not large tables, nor will this sp be run often.
> Rather than the subquery, I've read I should use temp table if necessary,
> but in most cases I can avoid using them at all.
> (I know temp tables do not perform the same purpose as cursors)
> Thanks for any suggestions.|||> I always use cursors
Probably that explains why you can't cope with a 25 million row table. Set
based code is smarter, more scalable and easier to maintain than cursors.
Cursors are useful - if you don't know SQL.
David Portas
SQL Server MVP
--
"marcmc" wrote:
> I always use cursors and have not been sacked yet
> depends on what you want to have happen in the cursor and the volumes of
> data, they can be just as useful as in oracle.
> Having said that I also use temp tables too.
> "Lesley" wrote:
>|||Thanks David,
The wording used to create these tables has made it confusing.
Each row in the tbl_TableMaster contains the identity of a logical or
physical table - some self referencing, yes. The logical tables are childre
n
to the physical tables. The logical tables are the one's I'm deleting. The
rows that make up the logical child tables are held within a separate
phsyical table. That physical table will, in most situations, never be
deleted. I'm deleting the rows from the physical table that contains the
child rows & the row from tbl_TableMaster that identifies the logical child
table. That may have made it more confusing, but if you think there is
anything else that may apply to that situation please advise.
I will look into the recursive trigger, I have used a recursive call for
something similar and it worked well. I think that may serve my purpose.
Though I used a physical table to store my data - can I use a temp table whe
n
making a call on itself? I will also look into "Materialized Path" & "Neste
d
Sets" as I'm not familiar with them & they may serve my purpose.
Thanks again.
"David Portas" wrote:
> Looks like a cascaded DELETE to me. Have you considered using the ON DELET
E
> CASCADE option? Otherwise you can use a DELETE trigger.
> If your table has a self-referencing foreign key and represents a hierarch
y
> of some unknown depth then you'll possibly want to use a Recursive trigger
.
> However, there are other data models for this kind of hierarchy that may
> serve you better. Google for "Materialized Path" or "Nested Sets" if you
> aren't familiar with them.
> --
> David Portas
> SQL Server MVP
> --
>
> "Lesley" wrote:
>|||I'm not certain what you mean by "logical tables" in this context but it
sounds very much like a better design would solve your problems. You seem to
be confusing data with metadata.
David Portas
SQL Server MVP
--
"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:F0ADAD7B-7D6D-44FA-B76F-665317A51BAB@.microsoft.com...
> Thanks David,
> The wording used to create these tables has made it confusing.
> Each row in the tbl_TableMaster contains the identity of a logical or
> physical table - some self referencing, yes. The logical tables are
> children
> to the physical tables. The logical tables are the one's I'm deleting.
> The
> rows that make up the logical child tables are held within a separate
> phsyical table. That physical table will, in most situations, never be
> deleted. I'm deleting the rows from the physical table that contains the
> child rows & the row from tbl_TableMaster that identifies the logical
> child
> table. That may have made it more confusing, but if you think there is
> anything else that may apply to that situation please advise.
> I will look into the recursive trigger, I have used a recursive call for
> something similar and it worked well. I think that may serve my purpose.
> Though I used a physical table to store my data - can I use a temp table
> when
> making a call on itself? I will also look into "Materialized Path" &
> "Nested
> Sets" as I'm not familiar with them & they may serve my purpose.
> Thanks again.
>
> "David Portas" wrote:
>|||Thanks,
I can't change the design at this point. I have to work with what has
already been developed & in production for a while.
By logical tables - it's just a bunch of rows of data held in one table that
have something in common - indicated by one row in the master table. You're
right, logical table isn't the right term - that's what everyone calls them
here in that particular situation. Metadata would be a better way to
describe it I think.
I think I can work without the cursors from here on in.
Thanks for your help.
Lesley.
"David Portas" wrote:

> I'm not certain what you mean by "logical tables" in this context but it
> sounds very much like a better design would solve your problems. You seem
to
> be confusing data with metadata.
> --
> David Portas
> SQL Server MVP
> --
> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:F0ADAD7B-7D6D-44FA-B76F-665317A51BAB@.microsoft.com...
>
>|||Try something like this...
DECLARE @.ChildTableID INT
SELECT @.ChildTableID = 0x80000000
WHILE (1=1)
BEGIN
SELECT @.ChildTableID = MIN( TableID ) FROM tbl_TableMaster WHERE TableID >
@.ChildTableID
AND ParentID IN( SELECT TableID FROM tbl_TableMaster WHERE supplierID =
@.v_FromSupplierID )
IF @.ChildTableID IS NULL BREAK
EXEC sp_DeleteChild @.ChildTableID
END
It should run faster than the cursor and use less resources.
It also assumes that you never use "-2147483648" as a TableID.
"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:B8354BE6-3FDD-4917-BC11-A54302FBCDEB@.microsoft.com...
> Hi,
> I was an oracle programmer recently moved to MSSql. So I'm trying to
learn
> the right way. Oracle was very cursor oriented & MSSQl seems to seriously
> discourage using them at all wherever possible.
> In this situation here's what I roughly written, with cursors - it'll give
> you the direction I am going at least. I want to re-write properly
without
> cursors.
> Declare cur_DeleteChild Cursor Scroll For
> Select TableID
> from tbl_TableMaster
> Where ParentID in
> (Select TableID from tbl_TableMaster where
> supplierID = @.v_FromSupplierID)
> Open cur_DeleteChild
> Fetch First FROM cur_DeleteChild into @.ChildTableID
> While (@.@.Fetch_Status <> -1)
> Begin
> exec sp_DeleteChild @.ChildTableID
> END
> Fetch Next FROM cur_DeleteChild into @.ChildTableID
> Close cur_DeleteChild
> Deallocate cur_DeleteChild
> These are not large tables, nor will this sp be run often.
> Rather than the subquery, I've read I should use temp table if necessary,
> but in most cases I can avoid using them at all.
> (I know temp tables do not perform the same purpose as cursors)
> Thanks for any suggestions.|||Thank you very much. I've just returned to this project after a few ws &
this code will do the trick.
Thanks again.
"Rebecca York" wrote:

> Try something like this...
> DECLARE @.ChildTableID INT
> SELECT @.ChildTableID = 0x80000000
> WHILE (1=1)
> BEGIN
> SELECT @.ChildTableID = MIN( TableID ) FROM tbl_TableMaster WHERE TableID
>
> @.ChildTableID
> AND ParentID IN( SELECT TableID FROM tbl_TableMaster WHERE supplierID =
> @.v_FromSupplierID )
> IF @.ChildTableID IS NULL BREAK
> EXEC sp_DeleteChild @.ChildTableID
> END
> It should run faster than the cursor and use less resources.
> It also assumes that you never use "-2147483648" as a TableID.
>
> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:B8354BE6-3FDD-4917-BC11-A54302FBCDEB@.microsoft.com...
> learn
> without
>
>