Solved

Help with Constructing Stored procedure

Posted on 2009-07-04
11
169 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now