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.
