Stored Procedure Abfrage ob Eintrag vorhanden

[Metallicwar]

Hallo zusammen,
ich bin gerade dabei eine Stored Procedure zu schreiben, die mir anhand einer Bankleitzahl den Banknamen zurück gibt, oder anhand dem Banknamen die Bankleitzahl!
Hier mein bisheriger Code:
Dieser funktioniert schon so weit, dass ich eine Bankleitzahl eintragen kann und er mir den Banknamen zurückgibt, falls es einen gibt.
Aber wie bau ich das ein, dass ich beim Ausführen der Stored Procedure entweder eine PLZ (integer) oder einen Banknamen (char) angeben kann, nachdem gesucht werden soll. Und ich möchte nur einen Parameter übergeben.

Code:

PROCEDURE SP_ZMI_Bankdaten
   ( 
      Bankleitzahl Integer,
      o Memo OUTPUT
   ) 
BEGIN
  DECLARE @Bankleitzahl INTEGER;
  DECLARE @Bankname char (60);
  @Bankleitzahl = (SELECT Bankleitzahl FROM __input);
  @Bankname = (SELECT Bankname FROM Bankdaten WHERE Bankleitzahl = @Bankleitzahl);
  IF @Bankname <> '' then
      INSERT INTO __output VALUES('Bankname lautet: ' +@Bankname);
   ELSE
      INSERT INTO __output VALUES('Keinen passenden Eintrag gefunden.');
   END;

END;

Aufruf der Stored Procedure:

Code:

EXECUTE PROCEDURE SP_ZMI_Bankdaten(Bankleitzahl oder Bankname);

[joachimd]

Zitat:

Zitat von Metallicwar (Beitrag 1084834)

Hallo zusammen,
ich bin gerade dabei eine Stored Procedure zu schreiben, die mir anhand einer Bankleitzahl den Banknamen zurück gibt, oder anhand dem Banknamen die Bankleitzahl!

Ich würde stattdessen eher eine FUNCTION verwenden, da universeller einsetzbar und eh nur ein Ergebnis zurückkommt.

SQL-Code:

create function ZMI_Bankdaten(such cichar(100))
  returns Memo
begin
  declare @blz integer;
  declare @name string;
  try
    @blz=cast(such as sql_integer);
  catch all
    @name=trim(such);
  end try;
  if @blz is NULL then
    @blz=12345; //hier BLZ aus name ermitteln
  else
    @name='hallo test'; //hier name aus BLZ ermitteln
  endif;
  return trim(cast(@blz as sql_char))+' '+@name;
end;

Aufruf:

SQL-Code:

select  
  ZMI_Bankdaten('testbank eG'), 
  ZMI_Bankdaten('60069911') 
from system.iota;

[shmia]

Also mit einer Bankleitzahl lässt sich eine Bank eindeutig identifizieren.
Mit einem Bankname ist dies nicht möglich.
Man bräuchte mindestens noch die Postleitzahl als weiteres Kriterium.
Daher würde ich empfehlen doch zwei getrennte SPs zu verwenden.

Ausserdem sollte die SP nur die reinen Daten liefen; also ohne den
Text "Bankname lautet: ".

[p80286]

Muß die BLZ ein Integer sein?
Soweit ich weiß, wird damit nicht gerechnet und da würde ein Char-Typ sich doch eher anbieten.

Gruß
K-H

[Metallicwar]

das Feld in der Tabelle ist ein Integer Feld, daher wird auch ein Integer erwartet, oder nicht?

[Metallicwar]

Code:

FUNCTION ZMI_Bankdaten 
   ( 
   such cichar(100)
   )
   RETURNS Memo
BEGIN
  declare @Bankleitzahl integer;
  declare @Bankname string;
  try
    @Bankleitzahl=cast(such as sql_integer);
  catch all
    @Bankname=trim(such);
  end try;
  if @Bankleitzahl is NULL then
    @Bankleitzahl= (SELECT Bankleitzahl FROM Bankdaten WHERE Bankname = such);
  else
    @Bankname= (SELECT Bankname FROM Bankdaten WHERE Bankleitzahl = such);
  endif;
  return trim(cast(@Bankleitzahl as sql_char))+' '+@Bankname;
END;

Soo, beim Eingeben einer Zahl natürlich in '' (

Code:

DECLARE @test Memo;
@test = ZMI_Bankdaten('10000000');

bekomm ich folgende Fehlermeldung:
Invalid operand for operator: = **
Script error information: -- Location of error in the SQL statement is: 295 (line: 11 column: 16) Error in UDF: ZMI_Bankdaten

Bei Eingabe einer Bank (

Code:

DECLARE @test Memo;
@test = ZMI_Bankdaten('Bundesbank');

liefert er mir die entsprechende PLZ zurück. Dazu eine Frage, werden bei Functionen der Rückgabewerd nicht wie bei Prozeduren in der Messagebox angezeigt?

[p80286]

Könnte es sein, daß

Code:

SELECT Bankname FROM Bankdaten WHERE Bankleitzahl = such

ein NULL zurück liefert, was dann wieder in '' übersetzt werden müßte?

Gruß
K-H

[Metallicwar]

hmn, mir is jetzte nicht klar, wieso ich das wieder umwandeln muss?
@bankname is vom typ string
und bei meinem Select erhalte ich doch auch einen Text zurück ?!?

[joachimd]

Zitat:

Zitat von Metallicwar (Beitrag 1084884)

if @Bankleitzahl is NULL then
@Bankleitzahl= (SELECT Bankleitzahl FROM Bankdaten WHERE Bankname = such);
else
@Bankname= (SELECT Bankname FROM Bankdaten WHERE Bankleitzahl = such);

naja, 'such' ist string...'bankleitzahl' aber integer. Nimm doch einfach

SQL-Code:

@Bankleitzahl= (SELECT Bankleitzahl FROM Bankdaten WHERE Bankname = @bankname);

[Metallicwar]

Er knallt in der Zeile,

Code:

@Bankname= (SELECT Bankname FROM Bankdaten WHERE Bankleitzahl = such);

nicht in der @Bankleitzahl .....

wie gesagt mit der Fehlermeldung: Invalid operand for operator...

[Metallicwar]

ah ok, habs hinbekommen !!!

Code:

FUNCTION ZMI_Bankdaten 
   ( 
   suchwert cichar(100)
   )
   RETURNS Memo
BEGIN
  DECLARE @Bankleitzahl integer;
  DECLARE @Bankname string;
  TRY
    @Bankleitzahl=cast(suchwert as sql_integer);
  catch all
    @Bankname=trim(suchwert);
  END TRY;
  IF @Bankleitzahl is NULL then
    @Bankleitzahl= (SELECT Bankleitzahl FROM Bankdaten WHERE Bankname = @bankname);
  ELSE
    @Bankname= (SELECT Bankname FROM Bankdaten WHERE Bankleitzahl = @Bankleitzahl);
  ENDIF;
  return trim(cast(@Bankleitzahl as sql_char))+' '+@Bankname;

END;

Vielen Dank Herr Dürr!

[Metallicwar]

Doch noch eine Frage :)
Beim Aufruf der Function über Delphi, wie kann ich mir das Ergebnis der Query in einem Editfeld anzeigen lassen.
Bei gewöhnlichen Abfragen (Select NAME from BENUTZER WHERE NAME = Hugo) ist dies ja über

Delphi-Quellcode:

edtRückgabe.Text := qBankdaten.FieldByName('NAME').asString

möglich.
Wenn ich hier als Feld ZMI_Bankdaten angebe, sagt er, dass er das angegebene Feld nicht finden kann.
Wie kann ich das lösen?

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
begin
  qBankdaten.SQL.Text := 'SELECT ZMI_Bankdaten('+QuotedStr(edtSuchwert.Text)+') FROM system.iota';
  qBankdaten.Open;
  edtRückgabe.Text := // das Ergebnis der Query als Text an mein EditFeld übergeben
end;

[joachimd]

zuallererst würde ich Parameter verwenden, damit Formatprobleme und SQL injections umgangen werden. Dann muss man das unbekannte Feld der Selektion natürlich auch benamen - oder man greift auf dessen index zu:

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
begin
  qBankdaten.SQL.Text := 'SELECT ZMI_Bankdaten(:suchwert) AS BANKDATEN FROM system.iota';
  qBankdaten.ParamByName('suchwert').AsString:=edtSuchwert.Text;
  qBankdaten.Open;
  edtRückgabe.Text := qBankdaten.FieldByName('bankdaten').AsString;
  //oder
  edtRückgabe.Text := qBankdaten.Fields[0].AsString;
end;

[Metallicwar]

da funktioniert doch recht gut !!! :)
nur zeigt er mir in meinem Editfeld immer beides an, also '10000000 Bundesbank' ich möchte aber immer nur die zugehörige PLZ oder eben den zugehörigen Banknamen.
Naja werd ich wohl nochmal in der Function was ändern müssen.
Mal schaun, ob ich es selber hinbekommen ;)
Bis dahin, vielen Dank.
Falls ich's nicht hinbekomme, melde ich mich ;)

[Metallicwar]

ach, habs schon.
War ja nichts großes.

Code:

CREATE FUNCTION ZMI_Bankdaten 
   ( 
   suchwert cichar(100)
   )
   RETURNS Memo
BEGIN
  DECLARE @Bankleitzahl integer;
  DECLARE @Bankname string;
  TRY
    @Bankleitzahl=cast(suchwert as sql_integer);
  catch all
    @Bankname=trim(suchwert);
  END TRY;
  IF @Bankleitzahl is NULL then
    @Bankleitzahl= (SELECT Bankleitzahl FROM Bankdaten WHERE Bankname = @bankname);
   return trim(cast(@Bankleitzahl as sql_char));
  ELSE
    @Bankname= (SELECT Bankname FROM Bankdaten WHERE Bankleitzahl = @Bankleitzahl);
    return @Bankname;
  ENDIF;

END;

Vielen Dank für die Hilfe.
MfG

[Metallicwar]

doch nochmal ne Frage:
Habe die ganze Zeit zum Testen eine gekürzte Bankdatenliste verwendet, nur die ersten 20 Einträge der Liste, damit das Suchen nicht zu lange dauert. Verwende ich nun aber die vollständige Liste werden mir bei manchen BLZ mehrere Banken angezeigt und auch umgekehrt. Logischerweise knallt es dann:

Code:

SELECT sub-query returned more than one row.  with scalar CAST --

bei

Code:

SELECT Bankname, Bankleitzahl FROM Bankdaten WHERE Bankleitzahl = 10000000

erhalte ich einen Banknamen
bei

Code:

SELECT Bankname, Bankleitzahl FROM Bankdaten WHERE Bankname = 'Bundesbank'

erhalte ich mehrere Einträge (immer den selben Banknamen aber unterschiedliche Bankleitzahlen)

ich hab im Buch von Herrn Dürr mal nachgeschlagen, ob es evtl. eine Funktion gibt um mit dem ersten Ergebnis des Selects weiterzuarbeiten und bin auf

Code:

COALESCE(expr1, ..)

gestoßen "Liefert das erste ERgebnis der ARgumentenliste, welches nicht in NULL resultiert."

Kann ich mit dieser Funktion arbeiten, oder ist es auf einem anderen Weg zu lösen?

[joachimd]

Zitat:

Zitat von Metallicwar (Beitrag 1085093)

SELECT sub-query returned more than one row. with scalar CAST --

Dürfen mehrere Einträge zurückkommen (also: interessieren die)? Wenn ja, wäre in diesem Fall dann eine Stored Procedure richtig, da die ein komplettes Resultset liefert. Falls es nicht relevant ist und die erste Fundstelle einfach genommen werden soll, dann einfach ein max() oder top 1 einfügen, also zB

SQL-Code:

@Bankleitzahl= (SELECT top 1 Bankleitzahl FROM Bankdaten WHERE Bankname = @bankname);

oder

SQL-Code:

@Bankleitzahl= (SELECT max(Bankleitzahl) FROM Bankdaten WHERE Bankname = @bankname);

[Metallicwar]

Zitat:

Dürfen mehrere Einträge zurückkommen (also: interessieren die)? Wenn ja, wäre in diesem Fall dann eine Stored Procedure richtig, da die ein komplettes Resultset liefert

ja sind interessant, dann müsst ich wohl doch wieder eine Stored Procedure schreiben ...
kann ich diesen Code nicht einfach übernehmen?

würde es mir dann so vorstellen, dass ich alle Bankleitzahlen + Bankname in eine Listbox oder in ein Memo schreibe.

[joachimd]

geht viel einfacher als über functions oder storedprocs:

Delphi-Quellcode:

query.sql.text:='select * from bankdaten where contains(bankname,:such1) or cast(bankleitzahl as sql_char) like :such2';
query.parambyname('such1').asstring:=edtSuchwert.Text;
query.parambyname('such2').asstring:=edtSuchwert.Text;
query.open;

[Metallicwar]

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
var
  sZeichenLoeschen: String;
  iDatensätze: Integer;
begin
  qBankdaten.sql.text:='select * from bankdaten where contains(bankname,:such1) or cast(bankleitzahl as sql_char) like :such2';
  qBankdaten.parambyname('such1').asstring:=edtSuchwert.Text;
  qBankdaten.parambyname('such2').asstring:=edtSuchwert.Text;
  qBankdaten.open;
  for iDatensätze := 0 to qBankdaten.{?} - 1 do
  // Alle Ergebnisse in ein Memo schreiben Memo1.Lines.Add(Bankleitahl + Bankname)

könnte mir nochmal jmd weiterhelfen?
wenn ich nur

Delphi-Quellcode:

Memo1.Lines.Add(qBankdaten.FieldByName('bankleitzahl').AsString);

zeigt er mir nur eine Bankleitzahl an, aber das will ich ja nicht !

[p80286]

Zitat:

Zitat von Metallicwar (Beitrag 1085150)

wenn ich nur

Delphi-Quellcode:

Memo1.Lines.Add(qBankdaten.FieldByName('bankleitzahl').AsString);

zeigt er mir nur eine Bankleitzahl an, aber das will ich ja nicht !

Und warum schreibst Du dann "bankleitzahl"?

entweder Du holst alles über Fields /Fieldcount ab, hat den Vorteil, daß Du den Namen nicht kennen mußt,
oder du mußt alle zurückgegebenen Felder mit Namen ansprechen:

Delphi-Quellcode:

Memo1.Lines.Add(qBankdaten.FieldByName('Feld1').AsString);
Memo1.Lines.Add(qBankdaten.FieldByName('Feld2).AsString);
..
Memo1.Lines.Add(qBankdaten.FieldByName('FeldN').AsString);

Gruß
K-H

[Metallicwar]

irgendwie hab ich grade einen Hänger :oops:

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
var
  sZeichenLoeschen: String;
  iDatensätze: Integer;
begin
  qBankdaten.sql.text:='select * from bankdaten where contains(bankname,:such1) or cast(bankleitzahl as sql_char) like :such2';
  qBankdaten.parambyname('such1').asstring:=edtSuchwert.Text;
  qBankdaten.parambyname('such2').asstring:=edtSuchwert.Text;
  qBankdaten.open;
  for iDatensätze := 0 to qBankdaten.FieldCount - 1 do
  begin
    Memo1.Lines.Add(qBankdaten.FieldByName('Bankleitzahl').AsString + '= ' +  qBankdaten.FieldByName('Bankname').AsString);
    qBankdaten.Next;
  end;
end;

Er trägt mir irgendwie immer drei Bankleitzahlen ein + den zugehörigen Banknamen, aber es müssen etliche mehr sein, was mach ich denn noch falsch? :x

Und wenn ich anstatt eines Banknamens eine PLZ eintrage (und diese ist ja eindeutig) krieg ich trotzdem 3 mal die selbe BLZ + Banknamen angezeigt.

[DeddyH]

Zitat:

Delphi-Quellcode:

for iDatensätze := 0 to qBankdaten.FieldCount - 1 do
  begin
    Memo1.Lines.Add(qBankdaten.FieldByName('Bankleitzahl').AsString + '= ' + qBankdaten.FieldByName('Bankname').AsString);
    qBankdaten.Next;
  end;

Was soll denn die For-Schleife?

[Metallicwar]

Zitat:

Was soll denn die For-Schleife?

Mein Select könnte ja mehrere Ergebnise beinhalten z.B.

Code:

1     Testbank
2     Testbank
3     Testbank
4     Testbank
5     Testbank

Und durch die for Schleife, wollte ich eben, erst das erste Ergebnis ins Memo schreiben, dann durch .Next das nächste Ergebnis, usw. ich hoffe ihr versteht wie ich's vorhatte.

[DeddyH]

Delphi-Quellcode:

while not qBankdaten.EOF do
  begin
    Memo1.Lines.Add(qBankdaten.FieldByName('Bankleitzahl').AsString + '= ' + qBankdaten.FieldByName('Bankname').AsString);
    qBankdaten.Next;
  end;

erscheint mir wesentlich zielführender ;)

[Metallicwar]

Zitat:

Zitat von DeddyH (Beitrag 1085182)

Delphi-Quellcode:

while not qBankdaten.EOF do
  begin
    Memo1.Lines.Add(qBankdaten.FieldByName('Bankleitzahl').AsString + '= ' + qBankdaten.FieldByName('Bankname').AsString);
    qBankdaten.Next;
  end;

erscheint mir wesentlich zielführender ;)

mir im Nachhinein auch :lol: :lol:

Vielen Dank, super Lösung!!!!!

Das wars !!! (falls mir nicht wieder irgendwas auffällt) *grins*