Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Can somebody please guide me on how to convert the following bit of Perl into a MS SQL Server stored procedure? 

sub calculating_pop_summations_A {

  my $sthZK_B = $dbh->prepare("

 SELECT DISTINCT Aggregated_area
 FROM Geo_postaggregated_pop_count;

          ") or die "Couldn't prepare query: ".$dbh->errstr;
  $sthZK_B->execute() or die "Couldn't execute query: ".$sthZK_B->errs
+tr;

  my @sthZK_B_A_array;

  while ( my @columns_B = $sthZK_B->fetchrow() ) {

    my $Aggregated_area = $columns_B[0];

# The /3 is required because the  

    my $sthZK_B_A = "


 UPDATE Geo_postaggregated_pop_count
 SET Sum_male_count =
        (SELECT SUM(G.Person_count)
      FROM Geo_postaggregated_pop_count G
      WHERE Sex = 1 AND Aggregated_area LIKE '".$Aggregated_area."')
 FROM Geo_postaggregated_pop_count
 WHERE Aggregated_area LIKE '".$Aggregated_area."';


 UPDATE Geo_postaggregated_pop_count
      SET Sum_female_count =
              (SELECT SUM(G.Person_count)
            FROM Geo_postaggregated_pop_count G
            WHERE Sex = 2 AND Aggregated_area LIKE '".$Aggregated_area
+."')
 FROM Geo_postaggregated_pop_count
 WHERE Aggregated_area LIKE '".$Aggregated_area."';

";

    push (@sthZK_B_A_array, $sthZK_B_A);
  }
  
  foreach (@sthZK_B_A_array){

    my $sthZK_C = $dbh->prepare("
             ".$_."
          ") or die "Couldn't prepare query: ".$dbh->errstr;
    $sthZK_C->execute() or die "Couldn't execute query: ".$sthZK_C->er
+rstr; 
  }

}

[download]

Replies are listed 'Best First'.
Re: Conversion to SPROC
by mpeppler (Vicar) on Jun 17, 2004 at 11:13 UTC
    I think it can be done in a single query, but a three step operation that maybe is a little more understandable could look like this: 
    create proc calculating_pop_summations_A
as
SELECT Sum_count = sum(Person_count), Sex, Aggregated_area
  INTO #tmp
  FROM Geo_postaggregated_pop_count
 GROUP BY Sex, Aggregated_area

update Geo_postaggregated_pop_count
   SET Sum_female_count = t.Sum_count
  FROM Geo_postaggregated_pop_count G
     , #tmp t
 WHERE G.Aggegated_area = t.Aggregated_area
   AND G.Sex = t.Sex
   AND G.Sex = 2

update Geo_postaggregated_pop_count
   SET Sum_male_count = t.Sum_count
  FROM Geo_postaggregated_pop_count G
     , #tmp t
 WHERE G.Aggegated_area = t.Aggregated_area
   AND G.Sex = t.Sex
   AND G.Sex = 1

    [download]
    Obviously untested, but I think this should produce the right results.

    Michael

[reply]
[d/l]
      With the exception of a spelling mistake this works very well. Thanks. If you enjoyed working out that, or for those that would like another go, I have have another bit of code along similar lines. As follows: 
      
sub calculating_pop_summations_B {

 

  my $sthZK_B = $dbh->prepare("

 SELECT DISTINCT Aggregated_area
 FROM Geo_postaggregated_pop_count;

          ") or die "Couldn't prepare query: ".$dbh->errstr;
  $sthZK_B->execute() or die "Couldn't execute query: ".$sthZK_B->errs
+tr;

  my @sthZK_B_A_array;

  while ( my @columns_B = $sthZK_B->fetchrow() ) {

    my $Aggregated_area = $columns_B[0];

 
    my $sthZK_B_A = "

 UPDATE Geo_postaggregated_pop_count
            SET Sum_of_person_count = (

 SELECT DISTINCT (SELECT SUM(G.Person_count)
            FROM Geo_postaggregated_pop_count G
            WHERE G.Sex = 1 AND G.Aggregated_area LIKE '".$Aggregated_
+area."')+
 (SELECT SUM(G.Person_count)
            FROM Geo_postaggregated_pop_count G
            WHERE G.Sex = 2 AND G.Aggregated_area LIKE '".$Aggregated_
+area."')
 FROM Geo_postaggregated_pop_count G
            WHERE G.Aggregated_area LIKE '".$Aggregated_area."')
 FROM Geo_postaggregated_pop_count G
            WHERE G.Aggregated_area LIKE '".$Aggregated_area."'


";

    push (@sthZK_B_A_array, $sthZK_B_A);

  }
  
  
  foreach (@sthZK_B_A_array){

    
    my $sthZK_C = $dbh->prepare("
             ".$_."
          ") or die "Couldn't prepare query: ".$dbh->errstr;
    $sthZK_C->execute() or die "Couldn't execute query: ".$sthZK_C->er
+rstr; 
  }


}

      [download]
[reply]
[d/l]
        True to my word. A lesson in keeping it simple I think. 
        
SELECT Sum_count = sum(Person_count), Aggregated_area
  INTO #tmp_B
  FROM Geo_postaggregated_pop_count
 GROUP BY Aggregated_area;


 UPDATE Geo_postaggregated_pop_count
   SET Sum_of_person_count = t.Sum_count
   FROM Geo_postaggregated_pop_count G
     , #tmp_B t
 WHERE G.Aggregated_area = t.Aggregated_area

        [download]
[reply]
[d/l]
        I will have a go myself also.
[reply]

Back to Seekers of Perl Wisdom