Übergabe NULL-Werte an Stored Procedure

[Jürgen Schaffroth]

Hallo,

folgendes Problem:

Ich habe eine Stored Procedure unter MS SQL Server 2000 erstellt, die Zeilen in eine Tabelle einfügt; der Aufruf erfolgt aus einem Delphi-Programm (geschrieben mit der Vers. 5 Prof). Insgesamt werden bis zu 10 Parameter an die Stored Proc. übergeben, davon werden vier Parameter mit dem Default-Wert NULL belegt. Wenn alle Parameter vorhanden und gefüllt sind, klappt die Sache wunderbar. Lasse ich einen oder mehrere Parameter weg, bekomme ich den Fehlerhinweis, dass der letzte der Parameter nicht vorhanden ist (der letzte ist ein Pflicht-Parameter).

Rufe ich die Stored Proc. aus dem Query Analyzer auf, kann ich problemlos die nicht bekannten Werte weglassen und die Zeile wird entsprechend mit NULL-Werten in die Tabelle eingetragen.

Kennt jemand dieses Problem bzw. weiß Abhilfe?

Danke.
Jürgen Schaffroth

[Andreas Kosch]

Hallo,

>..kann ich problemlos die nicht bekannten Werte weglassen...

wie sieht das am konkreten Beispiel aus? Bei mir verhält sich die Sache so:

<b>Schritt 1: MS SQL Server-Datenbank vorbereiten</b>
<pre>
USE tempdb
<b>GO</b>
<br>
<b>CREATE</b> <b>TABLE</b> SPDemoTbl
(
recid <b>INTEGER</b> <b>NOT</b> <b>NULL</b> <b>IDENTITY</b> <b>PRIMARY</b> <b>KEY</b>,
wert1 <b>VARCHAR</b>(9) <b>NULL</b>,
wert2 <b>INTEGER</b> <b>NULL</b>,
wert3 <b>INTEGER</b> <b>NULL</b>
)
<b>GO</b>
<br>
<b>CREATE</b> <b>PROCEDURE</b> spSPDemoTblInsert
(
@wert1 <b>VARCHAR</b>(9) = <b>NULL</b>,
@wert2 <b>INTEGER</b> = <b>NULL</b>,
@wert3 <b>INTEGER</b>
)
<b>AS</b>
<b>INSERT</b> <b>INTO</b>
dbo.SPDemoTbl (wert1,wert2,wert3)
<b>VALUES</b> (@wert1,@wert2,@wert2)
<b>RETURN</b> @@<b>IDENTITY</b>
<b>GO</b>
</pre>
<b>Schritt 2: Standard-Verhalten testen</b><br>
Der folgende Aufruf ist erfolgreich, der neue Datensatz wird in der Tabelle angelegt:
<pre>
<b>DECLARE</b> @NewRECID <b>INTEGER</b>
<b>EXEC</b> @NewRECID = dbo.spSPDemoTblInsert <b>NULL</b>, <b>NULL</b>, 1
PRINT @NewRECID
</pre>
<b>Schritt 3: Sonderfall testen</b><br>
Es wird nur 1 Parameter übergeben:
<pre>
<b>DECLARE</b> @NewRECID <b>INTEGER</b>
<b>EXEC</b> @NewRECID = dbo.spSPDemoTblInsert 2
PRINT @NewRECID
</pre>
Ergebnis: Fehlermeldung "<i>Server: Nachr.-Nr. 201, Schweregrad 16, Status 3, Prozedur spSPDemoTblInsert, Zeile 0<br>
Die spSPDemoTblInsert-Prozedur erwartet den @wert3-Parameter; dieser wurde nicht übergeben.</i&gt

[Jürgen Schaffroth]

Hallo,

ich bin im Query-Analyzer ähnlich vorgegangen:

Zuerst das Declare der Variablen,
dann der Aufruf der Stored Procedure wie folgt:

exec @iResult prInsertPerson @sAnrede = 'Herrn', @sVorname = 'Hugo', @sZuname = 'Tester' etc.

Die Spalten/Variablen, bei denen der Default NULL ziehen soll, habe ich weggelassen. Das funktioniert auch. <BR> Was nicht funktioniert, ist der Aufruf aus der Delphi-Applikation. Dort erstelle ich eine Parameter-Kollektion und lasse dort ebenfalls die Parameter weg, für die der Standardwert NULL gelten soll und weise anschließend die Werte zu. Dort erhalte ich eine analoge Fehlermeldung: Die prInsertPerson-Prozedur erwartet den @sAbteilung-Parameter, dieser wurde nicht übergeben (es handelt sich dabei um den letzten Parameter, den die Prozedur erwartet).

Danke für die Unterstützung <BR> Jürgen Schaffrot

[Andreas Kosch]

Hallo,

&gt;.. habe ich weggelassen. Das funktioniert auch.

aber nur, wenn <b>benannte</b>Parameter übergeben werden. Wenn man das in Delphi auch so machen will, stehen 2 Wege zur Verfügung: <br>
1. Eigenschaft <b>NamedParameters</b> aktivieren <br>
2. Stored Procedure direkt über TADOConnection ausführen lassen (EXEC)

Weg 1:
<pre>
<b>procedure</b> TForm1.ButtonNamedParametersClick(Sender: TObject);
<b>var</b>
aConnection : _Connection;
aCommand : _Command;
vRowsAffected: OleVariant;
iReturnValue : Integer;
<br>
<b>procedure</b> AddParamObj(<b>const</b> sName: <b>String</b>; iType,iParam,iSize: Integer);
<b>var</b>
aParam : _Parameter;
<b>begin</b>
<b>with</b> aCommand <b>do</b>
<b>begin</b>
aParam := CreateParameter(sName, iType, iParam,iSize, EmptyParam);
Parameters.Append(aParam);
<b>end</b>;
<b>end</b>;
<br>
<b>begin</b>
aConnection := CoConnection.Create;
aConnection.Open(cCS, <font color="#9933CC">''</font>, <font color="#9933CC">''</font>, adConnectUnspecified);
<b>try</b>
aCommand := CoCommand.Create;
<b>try</b>
<b>with</b> aCommand <b>do</b>
<b>begin</b>
NamedParameters := True;
CommandType := adCmdStoredProc;
Set_CommandText(<font color="#9933CC">'stprAddGInp'</font>);
<font color="#003399"><i>// Rückgabewert</i></font>
AddParamObj(<font color="#9933CC">'RETURN_VALUE'</font>, adInteger, adParamReturnValue, 4);
<font color="#003399"><i>// Input-Parameter</i></font>
AddParamObj(<font color="#9933CC">'@sNAME1'</font>, adVarChar, adParamInput, 40);
AddParamObj(<font color="#9933CC">'@sORT'</font>, adVarChar, adParamInput, 40);
AddParamObj(<font color="#9933CC">'@sPLZ'</font>, adChar, adParamInput, 5);
AddParamObj(<font color="#9933CC">'@sGENNR'</font>, adChar, adParamInput, 8);
<font color="#003399"><i>// Parameter-Werte zuweisen</i></font>
Parameters[<font color="#9933CC">'@sNAME1'</font>].Value := <font color="#9933CC">'Mustermann'</font>;
Parameters[<font color="#9933CC">'@sPLZ'</font>].Value := <font color="#9933CC">'53001'</font>;
Parameters[<font color="#9933CC">'@sORT'</font>].Value := <font color="#9933CC">'Bonn'</font>;
Parameters[<font color="#9933CC">'@sGENNR'</font>].Value := <font color="#9933CC">'89010104'</font>;
<font color="#003399"><i>// Ausführen</i></font>
Set_ActiveConnection(aConnection);
Execute(vRowsAffected, EmptyParam, adExecuteNoRecords);
iReturnValue := Parameters.Item[0].Value;
<b>end</b>;
<b>finally</b>
aCommand := <b>nil</b>;
<b>end</b>;
<b>finally</b>
aConnection.Close;
aConnection := <b>nil</b>;
<b>end</b>;
ShowMessage(IntToStr(iReturnValue) + <font color="#9933CC">' - '</font> + IntToStr(vRowsAffected));
<b>end</b>;
</pre&gt

[Jürgen Schaffroth]

Hallo,

die Sache mit den "NamedParameters" war der Knackpunkt. Ich habe mein Programm jetzt angepasst und nun funktioniert die Sache.

Danke für die Unterstützung.<BR>Jürgen Schaffrot