Click here to Skip to main content
12,300,841 members (61,905 online)
Rate this:
 
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 12-Oct-12 16:30pm
   
Okay, and?
Sergey Alexandrovich Kryukov 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 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 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 12-Oct-12 16:33pm
   
clear your question
Sergey Alexandrovich Kryukov 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 14 Oct 2012
Copyright © CodeProject, 1999-2016
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