Unassigned in OUTPUT parameter

[gootchick]

I decide to convert my db engine from ADO to SDAC. I realized different behavior of returned OUTPUT parameters from stored procedures. In this case, returned value of AIUD variable was NULL in ADO engine, but Unassigned in SDAC. I can fix it directly in this place, but these places in my code are really frequent. Where is the problem?

here is the code in Delphi2010 (TlLowStoredProc is successor of TMSStoredProc, AUID is OleVariant):

Code: Select all

LStored := TlLowStoredProc.Create(nil);
  try
    try
      with LStored do
      begin
        StoredProcName := sSP_LoginUser;
        Connection := AConnection;
        PrepareSQL;
        ParamByName(sSP_ALogin).Value := ALogin;
        ParamByName(sSP_APass).Value := APass;
        ParamByName(sSP_AUID).Value := Null;
        ExecProc;
        AUID := ParamByName(sSP_AUID).Value;
        Result := ParamByName(AConnection.ReturnField).Value;
      end;
    except
    end;
  finally
    LStored.Free;
  end

and here is the stored procedure in MSSQL 2005/2008:

Code: Select all

CREATE PROCEDURE DSP_LoginUser
@ALogin nvarchar(50),
@APass nvarchar(50),
@AUID uniqueidentifier OUTPUT
AS
  SET NOCOUNT ON

  SET @AUID = NULL

  SELECT @AUID = User.UID 
  FROM dbo.User 
  WHERE User.LOGIN = @ALogin AND User.PASS= @APass AND User.DELETED = 0

RETURN (@@ERROR)

[Dimon]

SDAC sets value to Unassigned and the IsNull property to True for empty parameters. Therefore to solve the problem you can use the following code:

Code: Select all

  if ParamByName(sSP_AUID).IsNull then
    AUID := Null
  else
    AUID := ParamByName(sSP_AUID).Value;