Solved

Help with Constructing Stored procedure

Posted on 2009-07-04
11
170 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

0
Comment
Question by:johny_bravo1
  • 7
  • 4
11 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24779350
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
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24779636
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.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24779639
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
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24779762
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..
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24782851
Whatever is suitable.I don't mind scrapping the old ones if new one is better.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24782966
Ok. Can you try this logic out:

1. Combine two procedures into one and based on the input either propertyno or khateno it should fetch accordingly.
2. Based upon the inputs either one of propertyno or khateno will be null and using that as a Filter condition do a select on either of the first query or the second query and return the result set.
3. If we modify as per your last requirement, then if you provide Khateno, then it should bring all khatedetails along with property owner details of those khate. In the reverse case, if you provide propertyno, it should bring property details along with khatedetails. So make it as a single temp table and try to fetch it out in a single SELECT query.

Questions:

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

Set @prop_owners = null
 
SELECT  @prop_owners = ISNULL(@prop_owners + ',', '') +  CAST (p.last_name+' '+p.first_name AS nVARCHAR(150))

Since prop_owners was set to Null, No need to check isnull condition and this should suffice

Set @prop_owners = '';
SELECT  @prop_owners = p.last_name+' '+p.first_name

And you can obtain this value in the earlier SELECT part itself instead of this cursor approach and an Update on the temp table can be avoided using this approach.

Select distinct g.property_id,p.last_name+' '+p.first_name 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,p.last_name)
And       p.first_name = Coalesce(@firstname,p.first_name)
And       p.middle_name = Coalesce(@middlename,p.middle_name)

I can actually provide you the modified procedure but request you to try and come up with errors and I can help you out on it which I feel is actually helping you to learn it.

Hope this makes sense
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24783012
---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.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24783142
>> 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
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24783184
--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

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24783276
Please find the attachment
sample.txt
0
 
LVL 8

Author Closing Comment

by:johny_bravo1
ID: 31599875
Thanks for the logic
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question