Is it possible to pass a null value parameter to a MS SQL stored procedure? I need to be able to pass null values to a stored procedure. I've tried many things but none of them seem to work. Has anyone been able to do this successfully? I am including my code and the trace below.

CODE>>>>>>>>>>>>>>
-SET &ECHO = ALL;
-SET &PoolStartDate = '';
-SET &PoolEndDate = '';
-SET &SecuritizationDate = '2017-02-28 23:59:59.000';
-SET &ServicingFeePct = 0.0;
-SET &DirectIndirectCostsPct = 0.0;
-SET &IsFranchise = '';
-SET &LoanTier = '';
-SET &CivilianOnly = 1;
-SET &DateQuarter = '2015 Q1';
-SET &DealerNumber = '';
-SET &HistoricalCurveStartDate = '2012-01-01';
-SET &HistoricalCurveEndDate = '2013-12-31';

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES SqlPassThruSecuritization SIERRA/,;Securitization
SQL SQLMSS SET NOCOUNT ON
SQL SQLMSS SET ANSI_WARNINGS OFF
SQL SQLMSS EX Securitization.Report.CashFlow
'&PoolStartDate',
'&PoolEndDate',
'&SecuritizationDate',
&ServicingFeePct,
&DirectIndirectCostsPct,
'&IsFranchise',
'&LoanTier',
&CivilianOnly,
'&DateQuarter',
'&DealerNumber',
'&HistoricalCurveStartDate',
'&HistoricalCurveEndDate';

TABLE FILE SQLOUT
PRINT *
END

TRACE>>>>>>>>>>>>>>
-SET &PoolStartDate = '';
-SET &PoolEndDate = '';
-SET &SecuritizationDate = '2017-02-28 23:59:59.000';
-SET &ServicingFeePct = 0.0;
-SET &DirectIndirectCostsPct = 0.0;
-SET &IsFranchise = '';
-SET &LoanTier = '';
-SET &CivilianOnly = 1;
-SET &DateQuarter = '2015 Q1';
-SET &DealerNumber = '';
-SET &HistoricalCurveStartDate = '2012-01-01';
-SET &HistoricalCurveEndDate = '2013-12-31';

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES SqlPassThruSecuritization SIERRA/,;Securitization
SQL SQLMSS SET NOCOUNT ON
SQL SQLMSS SET ANSI_WARNINGS OFF
SQL SQLMSS EX Securitization.Report.CashFlow
' ',
' ',
'2017-02-28 23:59:59.000',
0.0,
0.0,
' ',
' ',
1,
'2015 Q1',
' ',
'2012-01-01',
'2013-12-31';

TABLE FILE SQLOUT
PRINT *
END
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

This message has been edited. Last edited by: gerndude,

This should work:

-SET &ECHO = ALL;
-SET &PoolStartDate = 'null';
-SET &PoolEndDate = 'null';
-SET &SecuritizationDate = '2017-02-28 23:59:59.000';
-SET &ServicingFeePct = 0.0;
-SET &DirectIndirectCostsPct = 0.0;
-SET &IsFranchise = 'null';
-SET &LoanTier = 'null';
-SET &CivilianOnly = 1;
-SET &DateQuarter = '2015 Q1';
-SET &DealerNumber = 'null';
-SET &HistoricalCurveStartDate = '2012-01-01';
-SET &HistoricalCurveEndDate = '2013-12-31';

ENGINE SQLMSS SET CONNECTION_ATTRIBUTES SqlPassThruSecuritization SIERRA/,;Securitization
SQL SQLMSS SET NOCOUNT ON
SQL SQLMSS SET ANSI_WARNINGS OFF
SQL SQLMSS EX Securitization.Report.CashFlow
&PoolStartDate,
&PoolEndDate,
'&SecuritizationDate',
&ServicingFeePct,
&DirectIndirectCostsPct,
&IsFranchise,
&LoanTier,
&CivilianOnly,
'&DateQuarter',
&DealerNumber,
'&HistoricalCurveStartDate',
'&HistoricalCurveEndDate';

TABLE FILE SQLOUT
PRINT *
END

You may require some Dialogue Manager code if these variables could contain null or some other value as passed by a prior process, but if they are constantly null, this should work.

I have tried that. This is the error message I get.

(FOC1400) SQLCODE IS 8114 (HEX: 00001FB2) XOPEN: 22018
: Microsoft OLE DB Provider for SQL Server: [22018] Error converting data
: type varchar to datetime.
L (FOC1405) SQL PREPARE ERROR.

It seems that WF will not allow me to pass a variable that does not match the format in the stored procedure. If you execute the stored procedure directly inside MSSQL without WF, it will accept the 'null' value. Any suggestions on how this can be overcome?

That is a SQL error message, not a WebFOCUS one. Is the null in quotes? It shouldn't be.

Francis, Thanks so much for your wise help on this. I have been struggling with this for one week. I finally have results. Awesome!