On Thu, 24 Aug 2000, Hiroshi Inoue wrote:
> Hmm,Andreas's original function seems to contain other statements.
> If the function contains DML statements for the table Temp_Num_Table,
> it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
That's the problem. I'm in the process of porting a set of about
30 Stroed Procedures from MS-SQL server to PostgreSQL and have
just managed only 3 :-( because of some "This is not possible with
PostgreSQL".
To make clear what I want to do, I just copy the original stored
procedure, which might be not so clever so that it would be possible
to go without the table creation:
CREATE Procedure TilleA.pHelpCountNames
( @Arbeitsgruppeshort varchar(255) ,
@Condition varchar(255)
)
/* Count names in table Mitarbeiter which belong to the group
Arbeitsgruppeshort and match the condition @Condition
*/
As
Declare @Query varchar(1024)
Declare @num int
Select @num = 0
if @ArbeitsgruppeShort is NULL or @ArbeitsGruppeShort = '' begin
Select @ArbeitsgruppeShort = ''
end else begin
Select @ArbeitsgruppeShort = ' AND a.ArbeitsgruppeShort = ' + '''' +
@ArbeitsgruppeShort + ''''
end
Create Table #NumTable
(
Num integer
)
Select @Query =
'Insert Into #NumTable (Num) ' +
'SELECT Count (*) ' +
'FROM Mitarbeiter m ' +
'INNER JOIN tm2nMitarbeiterArbeitsgruppe t ON m.IdMitarbeiter =
t.IdMitarbeiter ' +
'INNER JOIN Arbeitsgruppe a ON t.IdArbeitsgruppe =
a.IdArbeitsgruppe ' +
'WHERE ' + @Condition + @ArbeitsgruppeShort
Exec (@Query)
Select @num=(Select Max(Num) From #NumTable)
Drop Table #NumTable
return @num
May be there are some clever ways to avoid the temporary table.
I really wonder if my solution is in fact very clever because I'm unsure
if it is thread-safe.
Any hints?
Kind regards
Andreas.
