Johny Bravo
asked on
Help with Constructing Stored procedure
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.
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
ASKER
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.
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.
ASKER
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
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
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..
Do you want a new procedure to merge both these procedures into a new one
or
Modify the existing ones..
ASKER
Whatever is suitable.I don't mind scrapping the old ones if new one is better.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
---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.
---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.
>> 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
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
ASKER
--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
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
ENDASKER
Please find the attachment
sample.txt
sample.txt
ASKER
Thanks for the logic
>> 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