Solved

Help with Stored procedure(SQL 2005)

Posted on 2009-07-04
2
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Clone table from one server.database to another server.database 24 49
Adding items to a C# list incrementally 5 60
How to repeat the data 4 30
Need to find substring in SQL 5 41
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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