Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Passing Null to a Stored Procedure

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Passing Null to a Stored Procedure
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 8203 Gen 43
App Studio 8203 Gen 04232018
Hyperstage 8203M Gen 1320
MS Sequel Server 2014
Windows 10
Excel/AHTML/PDF/HTML
Chrome, IE11
 
Posts: 48 | Location: Carmel IN | Registered: September 26, 2008Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
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?


WebFOCUS 8203 Gen 43
App Studio 8203 Gen 04232018
Hyperstage 8203M Gen 1320
MS Sequel Server 2014
Windows 10
Excel/AHTML/PDF/HTML
Chrome, IE11
 
Posts: 48 | Location: Carmel IN | Registered: September 26, 2008Report This Post
Expert
posted Hide Post
That is a SQL error message, not a WebFOCUS one. Is the null in quotes? It shouldn't be.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Silver Member
posted Hide Post
Francis, Thanks so much for your wise help on this. I have been struggling with this for one week. I finally have results. Awesome!


WebFOCUS 8203 Gen 43
App Studio 8203 Gen 04232018
Hyperstage 8203M Gen 1320
MS Sequel Server 2014
Windows 10
Excel/AHTML/PDF/HTML
Chrome, IE11
 
Posts: 48 | Location: Carmel IN | Registered: September 26, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Passing Null to a Stored Procedure

Copyright © 1996-2020 Information Builders