Solved

Help with Stored procedure(SQL 2005)

Posted on 2009-07-04
2
177 Views
Last Modified: 2013-11-07
HI experts,
I am seeking for some help in the following SP.

e.g, When I pass '10' to the following proc,
Village       Khate No       Khatedars                                       Survey No       Area Acquired       
Kalkuhi       10             Dehankar Gunderao,Dehankar Rambhau       7               0.88             
Kalkuhi       10             Dehankar Gunderao,Dehankar Rambhau       8               0.87             

I want to hide the Village name,jhate no and khatedar names in the o/p
I mean it should be like this.
Village       Khate No       Khatedars                                          Survey No       Area Acquired       
Kalkuhi       10             Dehankar Gunderao,Dehankar Rambhau       7                 0.88             
                                                                                        8                 0.87             



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


0
Comment
Question by:johny_bravo1
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24777014
sql server 2005, you can do that without a cursor
select case when r = 1 then village else null end as Village
     , case when r = 1 then khate_no else null end as khate_no
     , case when r = 1 then Khatedars else null end as Khatedars
     , Survey_no
     , Area_Acquired 
FROM
(
select village, khate_no, Khatedars, Survey_No, Area_Acquired 
    , row_number() over (partition by village order by survey_no) r
  from yourtable
) sq
order by Village, r

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
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…

863 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

25 Experts available now in Live!

Get 1:1 Help Now