Click here to Skip to main content
12,819,619 members (37,041 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)

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)
Posted 12-Oct-12 11:23am
Updated 12-Oct-12 11:28am
Wes Aday 12-Oct-12 16:30pm
Okay, and?
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?
ridoy 12-Oct-12 16:33pm
clear your question
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.
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.
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.
Rate this: bad
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.

Rate this: bad
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
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

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

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.170308.1 | Last Updated 14 Oct 2012
Copyright © CodeProject, 1999-2017
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