Databases
StackOverflow2013
Postsdbo
POT-SQL Stored Procedure for Looping a hierarchy with recursion
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL Stored Procedure for Looping a hierarchy with recursion
    primarykey
    data
    text
    <p>i have a specific Question.</p> <p>My Stored Procedure don't work correctly.. I will show you the Procedure, the database and the parameter:</p> <pre><code>ALTER PROCEDURE [dbo].[ManufacturerParentToChild] @ServiceProviderId int, @CarmakerId int </code></pre> <p>AS BEGIN</p> <pre><code>SET NOCOUNT ON; DECLARE @childSPPId int, @isDeleted bit DECLARE ServiceProviderChildren_Cursor CURSOR FOR SELECT ServiceProviderId, isDeleted FROM ServiceProvider WHERE ParentServiceProviderId = @ServiceProviderId; OPEN ServiceProviderChildren_Cursor; FETCH NEXT FROM ServiceProviderChildren_Cursor INTO @childSPPId, @isDeleted; WHILE @@FETCH_STATUS = 0 BEGIN IF @ServiceProviderId &gt; 0 BEGIN EXEC ManufacturerParentToChild @childSPPId, @CarmakerId; IF (SELECT COUNT(*) FROM dbo.CarmakerPartnership WHERE ServiceProviderId = @childSPPId AND CarmakerId = @CarmakerId) = 0 BEGIN IF (@isDeleted = 0) BEGIN INSERT INTO dbo.CarmakerPartnership (CarmakerId, ServiceProviderId, CreatedBy, ChangedBy, ValidityPeriodFrom, ValidityPeriodTo) VALUES (@CarmakerId, @childSPPId, SYSTEM_USER, SYSTEM_USER, '01.01.1900 00:00:00', '31.12.9999 23:59:00.000') END END END FETCH NEXT FROM ServiceProviderChildren_Cursor INTO @childSPPId; END; CLOSE ServiceProviderChildren_Cursor; DEALLOCATE ServiceProviderChildren_Cursor; END </code></pre> <p>You See my stored Procedure above.</p> <p>The Database Table Service Provider have 7 Rows:</p> <p>ServiceProviderId Name ParentServiceProviderId isDeleted<br> 1 'Parent' Null 0 <br/> 2 'Child1' 1 0 <br/> 3 'Child2' 1 0 <br/> 4 'Child4' 2 0 <br/> 5 'Child5' 3 0 <br/> 6 'child6' 4 0 <br/> 7 'Child7' 6 0 <br/></p> <p>The Paramater get the Values:</p> <p>@ServiceProviderId = 1 @CarmakerId = 5</p> <p>The Procedure insert the ServiceProviderId 7, 6, 4 and 2 into CarmakerPartnerShip but don't insert 3 and 5!</p> <p>Have anybody a idea why the recursive loop jump trough the childs of ServiceProviderId = 2 but don't jump trough the childs of ServiceProviderId = 3 ??</p> <p>Thank you for your Help and Sorry for my bad english!!</p> <p>If you have Questions you can ask me.</p> <p>Best Regards </p> <p>Alex</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USAlexo
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COsorry, I don't do cursors!
      singulars
      1. POT-SQL Stored Procedure for Looping a hierarchy with recursion
      1. USMitch Wheat
    2. COUse `EXISTS` not `SELECT COUNT(*) ` to check for existence. This is more efficient. As would be ditching the cursors probably :-)
      singulars
      1. POT-SQL Stored Procedure for Looping a hierarchy with recursion
      1. USMartin Smith
    3. COWith any kind of recursion in SQL, I highly recommend looking at using a CTE(Common Table Expression)... You also are trying to do procedural programming in a stored procedure, which is seldom optimal...
      singulars
      1. POT-SQL Stored Procedure for Looping a hierarchy with recursion
      1. USsyllogism
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload