Solved

Help with Stored procedure(SQL 2005)

Posted on 2009-07-04
2
182 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 143

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…

829 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