Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
in the given procedure I would like to find the parentId for a particular MemId
Create PROCEDURE [dbo].[AddTempMember](@ParentId int,@ThroughOverId int, @Leg VARCHAR(50),@lleg varchar(50),@rleg varchar(50),@Title varchar(50),@FirstName varchar(50),@LastName varchar(50), @Type varchar(50),@FathersName varchar(50),@MothersName varchar(50), @MaritalStatus varchar(50), @Address varchar(50), @City varchar(50), @State varchar(50),@Country varchar(50),@Pin varchar(50),@Std varchar(50),@Phone varchar(50),@Mobile varchar(50),@Email varchar(50),@LoginId varchar(50),@Password varchar(50),@DOB date,@Age VARCHAR(50),@Sex varchar(50),@PAN varchar(50),@NomineeName varchar(50),@NomineeRelation varchar(50),@NomineeDOB varchar(50),@ProductCode varchar(50),@PaymentOption varchar(50),@PaymentMode varchar(50),@Amount money,@AmountBalance money,@BankName varchar(50),@DDChequeNo varchar(50),@DDChequeDate date,@Payable varchar(50),@MemberAccNo varchar(50),@MemberBankName varchar(50),@MemberBranch varchar(50),@DepotCode varchar(50),@ReceiptNo varchar(50),@JoiningDate varchar(50),@CreatedBy varchar(50),@MemId Varchar(50) output,@msg VARCHAR(50) output)
 
AS BEGIN
INSERT INTO Members(ParentId, ThroughOverId,Leg,lleg,rleg,Title,FirstName,LastName,Type,FathersName,MothersName,MaritalStatus,Address ,City,State,Country,Pin,Std,Phone,Mobile,Email,LoginId,Password,DOB,Age,Sex,PAN,NomineeName,NomineeRelation,NomineeDOB,ProductCode,PaymentOption,PaymentMode,Amount,AmountBalance,BankName,DDChequeNo,DDChequeDate,Payable,MemberAccNo,MemberBankName,MemberBranch,DepotCode,ReceiptNo,JoiningDate,CreatedBy,MemId,msg)
values (@ParentId,@ThroughOverId,@Leg,@lleg,@rleg,@Title,@FirstName,@LastName,@Type,@FathersName,@MothersName,@MaritalStatus,@Address ,@City,@State ,@Country,@Pin,@Std,@Phone,@Mobile,@Email,@LoginId,@Password,@DOB,@Age,@Sex,@PAN,@NomineeName,@NomineeRelation,@NomineeDOB,@ProductCode,@PaymentOption,@PaymentMode,@Amount,@AmountBalance,@BankName,@DDChequeNo,@DDChequeDate,@Payable,@MemberAccNo,@MemberBankName,@MemberBranch,@DepotCode,@ReceiptNo,@JoiningDate,@CreatedBy,@MemId,@msg)
END
Posted 12-Oct-12 10:23am
Edited 12-Oct-12 10:28am
v2
Comments
Wes Aday at 12-Oct-12 16:30pm
   
Okay, and?
Sergey Alexandrovich Kryukov at 12-Oct-12 17:29pm
   
This "and" may make no sense, because OP probably has a misconception. Whatever it is in reality, the problem is trivial -- please see my answer.
--SA
Marcus Kramer at 12-Oct-12 16:32pm
   
You are doing an insert and you are providing the procedure with the memId already so I'm a little confused as to what you need. Could you please clarify that?
Sergey Alexandrovich Kryukov at 12-Oct-12 17:28pm
   
As it looks like that OP does not understand the problem in first place, I added an answer explaining this. Whatever it is in reality, the problem is trivial -- please see.
--SA
ridoy at 12-Oct-12 16:33pm
   
clear your question
Sergey Alexandrovich Kryukov at 12-Oct-12 17:30pm
   
Whatever it is -- I answered, by the reasons I explained in other comments above -- please see them and my answer.
--SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

In a tree, each node has zero parents or just one, by definition. Most implementations provide access to the parent node from a child node. This is also a case with a correct properly normalized database schema: a relationship between child and parent node is implemented as an index of a child not record, which points to the parent node. This way, there is nothing to "find", the query is trivial.
 
—SA
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Your procedure adds a 'Member' into existing table. There's nothing to do with 'find'.
 
If you would like to 'find' (display/retrieve) all ParentId for MemId, use this query:
SELECT MemId, ParentId
FROM Members
ORDER BY MemId
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

DECLARE @FindParentsForMember INT;
SET  @FindParentsForMember = 757;
WITH Hierarchy(MemId,ParentId) 
AS(
     SELECT DISTINCT parent.MemId,parent.ParentId
     FROM Members parent
     WHERE parent.MemId = @FindParentsForMember
 
     UNION ALL
 
     SELECT child.MemId,child.ParentId
     FROM Members child
     INNER JOIN Hierarchy parent ON parent.ParentId = child.MemId
)
SELECT DISTINCT ParentId
FROM Hierarchy
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 250
1 OriginalGriff 210
2 Manfred R. Bihy 190
3 CHill60 180
4 _Amy 155
0 OriginalGriff 7,445
1 Sergey Alexandrovich Kryukov 6,177
2 Maciej Los 3,774
3 Peter Leow 3,478
4 CHill60 2,702


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 14 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100