We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Help with Constructing Stored procedure

Medium Priority
206 Views
Last Modified: 2013-11-07
Hi Experts,
It is hard for me to explain exactly the scenario but I'll try my best.
In my form I am having search accorind to,
Village Name,Khate no,Property No,Person name

Please have a look at following two procedures.
Both the procedures works fine.
But I require some extra stuff.
When I pass any Khate no,I want to get its details(which I am getting now) plus I want to show the property details of the person who are in holds that Khate no.
Similary when I pass proprty No,along with proprty details I want to fetch the data for the Khate no of those person which holds that property.

If you need any futher clarification pls let me know.
Your guidance is much needed.
Thanks in advance.
There is no relation between khate no and property no.
//Procedure for getting Khate No details 
CREATE PROCEDURE [dbo].[spKhateAcquisition_new]
	-- Add the parameters for the stored procedure here
	@khateno nvarchar(100) = null,
	@villageid int = null,
	@lastname nvarchar(100) = null,
    @firstname nvarchar(100) = null,
    @middlename nvarchar(100) = null
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
Declare @kno nvarchar(100)
DECLARE @khatenames nVARCHAR(4000) 
 
 
SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
	CREATE TABLE #temp
	(
	[khatedar_no] [nvarchar](100) NULL,
	[Khatedars] [nvarchar](1000) NULL,
	[new_survey_no] [nvarchar](50) NULL,
	[Village_Name] [nvarchar](50) NULL,
	[area_acquired] [float] NULL,
	[net_amount] [float] NULL
	)
 
Insert into #temp
Select distinct sk.khatedar_no,'xxxx' Khatedars,sk.new_servey_no,v.village_name,(s.area_acquired*sk.share_if_any)/100 as area_acquired,(s.net_amount*sk.share_if_any)/100
from tbperson p left outer join tbSurveryKhatedar sk on p.person_no = sk.person_no 
left outer join tbGaothanPerson gp on p.person_no = gp.person_no
inner join tbVillage v on v.village_id = sk.village_id
inner join tbSurvey s on s.village_id = sk.village_id And sk.new_servey_no = s.new_survey_no
Where sk.Village_id = Coalesce(@villageid,sk.Village_id)
And	  sk.khatedar_no = 	Coalesce(@khateno,khatedar_no)
And	  p.last_name = Coalesce(@lastname,last_name)
And	  p.first_name = Coalesce(@firstname,first_name)
And	  p.middle_name = Coalesce(@middlename,middle_name)
 
DECLARE db_cursor CURSOR FOR 
	SELECT khatedar_no
	FROM #temp
 
	OPEN db_cursor  
	FETCH NEXT FROM db_cursor INTO @kno
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
      Set @khatenames = null
 
		SELECT  @khatenames = ISNULL(@khatenames + ',', '') +  CAST (p.last_name+' '+p.first_name AS nVARCHAR(150))
		FROM tbPerson p
		Inner Join tbSurveryKhatedar sk
		On sk.person_no = p.person_no
		WHERE sk.khatedar_no =  Coalesce(@kno,khatedar_no) And sk.village_id = Coalesce(@villageid,village_id)
		GROUP BY p.last_name, p.first_name
 
	--	SELECT @khatenames AS new_survey_no     
 
		Update #temp set khatedars = @khatenames where khatedar_no = @kno 
		
		FETCH NEXT FROM db_cursor INTO @kno
END  
 
CLOSE db_cursor  
DEALLOCATE db_cursor
 
Select * from #temp
 
END
 
 
 
 
 
//Procedure to get Property no details
Create PROCEDURE [dbo].[spPropertyResettlement]
	-- Add the parameters for the stored procedure here
	@propertyno nvarchar(100) = null,
	@villageid int = null,
	@lastname nvarchar(100) = null,
    @firstname nvarchar(100) = null,
    @middlename nvarchar(100) = null
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
Declare @pno nvarchar(100)
DECLARE @prop_owners nVARCHAR(4000) 
 
 
SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
 
 
	CREATE TABLE #temp
	(
	[property_id] [nvarchar](100) NULL,
	[prop_owners] [nvarchar](1000) NULL,
	[Village_Name] [nvarchar](50) NULL,
	[Unit_no] [nvarchar](50) NULL,
	[area_acquired] [float] NULL,
	[net_amount] [float] NULL
	)
 
Insert into #temp
Select distinct g.property_id,'xxxx' prop_owners,v.village_name,g.khasara_no as Unit_no,g.area_acquired,g.net_amount
From tbVillage v inner join tbGaothan g
On v.village_id = g.village_id
inner join tbGaothanPerson gp on g.property_id = gp.property_id
inner join tbPerson p on p.person_no = gp.person_no
Where g.Village_id = Coalesce(@villageid,g.Village_id)
And	  g.property_id = 	Coalesce(@propertyno,g.property_id)
And	  p.last_name = Coalesce(@lastname,last_name)
And	  p.first_name = Coalesce(@firstname,first_name)
And	  p.middle_name = Coalesce(@middlename,middle_name)
 
DECLARE db_cursor CURSOR FOR 
	SELECT property_id
	FROM #temp
 
	OPEN db_cursor  
	FETCH NEXT FROM db_cursor INTO @pno
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
      Set @prop_owners = null
 
		SELECT  @prop_owners = ISNULL(@prop_owners + ',', '') +  CAST (p.last_name+' '+p.first_name AS nVARCHAR(150))
		FROM tbPerson p Inner Join tbGaothanPerson gp
		On gp.person_no = p.person_no
		WHERE gp.property_id =  Coalesce(@pno,property_id) 
		And p.last_name = Coalesce(null,last_name) And p.first_name = Coalesce(null,first_name) And p.middle_name = Coalesce(null,middle_name)
		GROUP BY p.last_name, p.first_name
 
	--	SELECT @khatenames AS new_survey_no     
 
		Update #temp set prop_owners = @prop_owners where property_id = @pno 
		
		FETCH NEXT FROM db_cursor INTO @pno
END  
 
CLOSE db_cursor  
DEALLOCATE db_cursor
 
Select * from #temp
 
END

Open in new window

Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Kindly provide the following informations:

>> When I pass any Khate no,I want to get its details(which I am getting now) plus I want to show the property details of the person who are in holds that Khate no.

Where do I get the property details of the person.

>> Similary when I pass proprty No,along with proprty details I want to fetch the data for the Khate no of those person which holds that property.

Where do I get the Khate no details of the person.

I hope first one is for first procedure and the second one is for second procedure.
Kindly confirm

Author

Commented:
Yup you are right,
The first stored procedure is for getting khatedar_no details.Which I am able to get.
Similarly for the second one I am able to get Property_details.

Now what I want is to get proerty details and khate details if I pass khatedar_no or property_no.

Author

Commented:
For reference have a look at table structure

1>tbGaothan
property_id PK
village_id
khasara_no
area_of_plot
area_acquired
net_amount

2>tbPerson
person_no PK
last_name
first_name
middle_name
address

3>tbGaothanPerson
property_id PK Fk
person_no PK FK

4>tbSurvey
village_id  PK
new_survey_no PK
area_acquired
net_amount

5>tbSurveryKhatedar
village_id PK FK
new_servey_no  PK FK
khatedar_no   PK
person_no PK FK
share_if_any
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Forgot to ask about this in my earlier question.

Do you want a new procedure to merge both these procedures into a new one
or
Modify the existing ones..

Author

Commented:
Whatever is suitable.I don't mind scrapping the old ones if new one is better.
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
---1. What is the relationship between propertyno and khateno
---2. What's the need of the cursor to update propertyname in the first procedure.

There is no direct relation with property_no and khate_no
Consider a case,if q,w,e,t are the four joint holder of khate_no 448.
Now again lets say w possess a property p1 and e possess a property p2.

Now if I pass khate_no 448 it should bring me the details of those khate holders and the persons who holds that khate_no,if have any property,in our case p1,p2 it should bring the property details of that p1,p2.

Your Next question was why I am using cursor.
Lets consider another example.If propertyno p1  is jointly hold by q,w,r then I need to show then as property owners in a single line with the other details of the property.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> Now if I pass khate_no 448 it should bring me the details of those khate holders and the persons who holds that khate_no,if have any property,in our case p1,p2 it should bring the property details of that p1,p2.

So if the property names are concatenated then how do you expect your other property details for it since that is your requirement if I am not wrong.

>> If propertyno p1  is jointly hold by q,w,r then I need to show then as property owners in a single line with the other details of the property.

If other details of that property varies for each and every property owner, then how it needs to be taken care.

It would be more helpful to me if you provide some sample records for your five tables and then the earlier procedures input and outputs along with modified ones input and outputs as you have wide range of scenarios

Author

Commented:
--So if the property names are concatenated then how do you expect your other property details for it --since that is your requirement if I am not wrong.

property_names are not concatenated,property owners are concatenated.

--If other details of that property varies for each and every property owner, then how it needs to be taken --care.

property is single and owners may be multiple.They jointly hold this property.
So property details are not going to change.

Anyways I was trying to wirte the procedure and I am quiete successful in it.
Now if I pass a khate_no,first I am fetching the khate_no details,then I am fetching the person ids of those khateno owners.Then I am searching those person id in tbGaothan and finally getting multiple owners as comma seperated.

I will provide you some sample data.Meanwhile please go through the following SP,I need your help to modify it a bit.
Thanks
ALTER PROCEDURE [dbo].[spKhateAcquisition_new_testing]
	-- Add the parameters for the stored procedure here
	@khateno nvarchar(100) = null,
	@villageid int = null,
	@lastname nvarchar(100) = null,
    @firstname nvarchar(100) = null,
    @middlename nvarchar(100) = null
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
Declare @kno nvarchar(100)
Declare @person_no nvarchar(100)
DECLARE @khatenames nVARCHAR(4000) 
Declare @pno nvarchar(500)
Declare @prop_owners nvarchar(2000)
 
DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int
 
 
 
SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
CREATE TABLE #tempProperty
			(
			[property_id] [nvarchar](100) NULL,
			[prop_owners] [nvarchar](1000) NULL,
			[Village_Name] [nvarchar](50) NULL,
			[Unit_no] [nvarchar](50) NULL,
			[area_acquired] [float] NULL,
			[net_amount] [float] NULL,
			person nvarchar(100) null,
			Village_type nvarchar(10) null
			)
 
 
	CREATE TABLE #temp
	(
	[khatedar_no] [nvarchar](100) NULL,
	[Khatedars] [nvarchar](1000) NULL,
	[new_survey_no] [nvarchar](50) NULL,
	[Village_Name] [nvarchar](50) NULL,
	[area_acquired] [float] NULL,
	[net_amount] [float] NULL,
	person nvarchar(100) null,
	Village_type nvarchar(10) null
	)
 
Insert into #temp
Select distinct sk.khatedar_no,'xxxx' Khatedars,sk.new_servey_no,v.village_name,(s.area_acquired*sk.share_if_any)/100 as area_acquired,(s.net_amount*sk.share_if_any)/100,'xxx' person,v.village_type
from tbperson p left outer join tbSurveryKhatedar sk on p.person_no = sk.person_no 
left outer join tbGaothanPerson gp on p.person_no = gp.person_no
inner join tbVillage v on v.village_id = sk.village_id
inner join tbSurvey s on s.village_id = sk.village_id And sk.new_servey_no = s.new_survey_no
Where sk.Village_id = Coalesce(@villageid,sk.Village_id)
And	  sk.khatedar_no = 	Coalesce(@khateno,khatedar_no)
And	  p.last_name = Coalesce(@lastname,last_name)
And	  p.first_name = Coalesce(@firstname,first_name)
And	  p.middle_name = Coalesce(@middlename,middle_name)
 
DECLARE db_cursor CURSOR FOR 
	SELECT  khatedar_no
	FROM #temp
 
	OPEN db_cursor  
	FETCH NEXT FROM db_cursor INTO @kno
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
      Set @khatenames = null
 
		SELECT  @khatenames = ISNULL(@khatenames + ',', '') +  CAST (p.last_name+' '+p.first_name AS nVARCHAR(150))
		FROM tbPerson p
		Inner Join tbSurveryKhatedar sk
		On sk.person_no = p.person_no
		WHERE sk.khatedar_no =  Coalesce(@kno,khatedar_no) And sk.village_id = Coalesce(@villageid,village_id)
		GROUP BY p.last_name, p.first_name
 
	--	SELECT @khatenames AS new_survey_no     
 
		Update #temp set khatedars = @khatenames where khatedar_no = @kno 
		
		FETCH NEXT FROM db_cursor INTO @kno
END  
 
CLOSE db_cursor  
DEALLOCATE db_cursor
 
--Start Cursor for comma seperated person ids For Khate Number
 
DECLARE db_cursor1 CURSOR FOR 
	SELECT distinct khatedar_no
	FROM #temp
 
	OPEN db_cursor1  
	FETCH NEXT FROM db_cursor1 INTO @kno
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
      Set @person_no = null
 
		SELECT  @person_no = ISNULL(@person_no + ',', '') +  CAST (p.person_no AS nVARCHAR(150))
		FROM tbPerson p
		Inner Join tbSurveryKhatedar sk
		On sk.person_no = p.person_no
		WHERE sk.khatedar_no =  Coalesce(@kno,khatedar_no) And sk.village_id = Coalesce(@villageid,village_id)
		GROUP BY p.person_no
 
		Update #temp set person = @person_no where khatedar_no = @kno 
		
		FETCH NEXT FROM db_cursor1 INTO @kno
END  
 
CLOSE db_cursor1 
DEALLOCATE db_cursor1
 
--End Comma Seperated Person Ids
 
--Fetching Property Details
DECLARE db_cursor2 CURSOR FOR 
	SELECT  person
	FROM #temp
 
OPEN db_cursor2  
	FETCH NEXT FROM db_cursor2 INTO @person_no
print 'next @person_no'
print @person_no
WHILE @@FETCH_STATUS = 0  
BEGIN  
		--set start position for pos		
		SELECT @pos = 1, @nextpos = 1
		Declare @tempperson int
		--Start While for commas
		WHILE @nextpos > 0
		BEGIN
			--Search next comma	
			SELECT @nextpos = charindex(',', @person_no,@pos)
			--check for the end of the string and get person accordingly
			If (@nextpos = 0)	
				Select @tempperson = convert(int, substring(@person_no, @pos,len(@person_no)))
			Else
				Select @tempperson = convert(int, substring(@person_no, @pos, @nextpos - @pos))
--print '@tempperson'
--print @tempperson
			--populate the temp property table
			insert into #tempProperty
			Select distinct g.property_id,'xxxx' prop_owners,v.village_name,g.khasara_no as Unit_no,g.area_acquired,g.net_amount,'ppp' person,v.village_type
			From tbVillage v inner join tbGaothan g
			On v.village_id = g.village_id
			inner join tbGaothanPerson gp on g.property_id = gp.property_id
			inner join tbPerson p on p.person_no = gp.person_no
			Where p.person_no = @tempperson
			--increment the pos value to the next place to current comma position
			Select @pos= @nextpos + 1
		End	
 
	FETCH NEXT FROM db_cursor2 INTO @person_no
END  
 
CLOSE db_cursor2 
DEALLOCATE db_cursor2
--End property Details
 
--Get property Owners
DECLARE db_cursor4 CURSOR FOR 
	SELECT property_id
	FROM #tempProperty
 
	OPEN db_cursor4 
	FETCH NEXT FROM db_cursor4 INTO @pno
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
      Set @prop_owners = null
 
		SELECT  @prop_owners = ISNULL(@prop_owners + ',', '') +  CAST (p.last_name+' '+p.first_name AS nVARCHAR(150))
		FROM tbPerson p Inner Join tbGaothanPerson gp
		On gp.person_no = p.person_no
		WHERE gp.property_id =  Coalesce(@pno,property_id) 
		
	--	SELECT @khatenames AS new_survey_no     
 
		Update #tempProperty set prop_owners = @prop_owners where property_id = @pno 
		
		FETCH NEXT FROM db_cursor4 INTO @pno
END  
 
CLOSE db_cursor4  
DEALLOCATE db_cursor4
--End Get Property Owners
 
 
--Start Cursor for comma seperated person ids For Property Number
 
DECLARE db_cursor5 CURSOR FOR 
	SELECT distinct property_id
	FROM #tempProperty
 
	OPEN db_cursor5  
	FETCH NEXT FROM db_cursor5 INTO @pno
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
      Set @person_no = null
 
		SELECT  @person_no = ISNULL(@person_no + ',', '') +  CAST (p.person_no AS nVARCHAR(150))
		FROM tbPerson p
		Inner Join tbGaothanPerson gp
		On gp.person_no = p.person_no
		WHERE gp.property_id =  Coalesce(@pno,property_id)
		GROUP BY p.person_no
 
		Update #tempProperty set person = @person_no where property_id = @pno 
		
		FETCH NEXT FROM db_cursor5 INTO @pno
END  
 
CLOSE db_cursor5 
DEALLOCATE db_cursor5
 
--End Comma Seperated Person Ids
 
 
Select * from #temp
Select distinct * from #tempProperty
 
END

Open in new window

Author

Commented:
Please find the attachment
sample.txt

Author

Commented:
Thanks for the logic
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.