Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with Stored procedure(SQL 2005)

Posted on 2009-07-04
2
Medium Priority
?
188 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 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