Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Handling potential nulls in stored procedure calls
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Handling potential nulls in stored procedure calls
 Login/Join
 
Silver Member
posted
Good Afternoon,

I am calling a stored procedure like so:

ENGINE SQLMSS SET DEFAULT_CONNECTION CONN_DEV
SQL SQLMSS
EX getPermissions '&&USER_ID', &&TEST_VAR;
TABLE FILE SQLOUT
PRINT *
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS 'foccache/hold_test' FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE OFF
END

TEST_VAR can potentially hold a guid or be null. If I'm understanding this correctly if it's null I have to pass the variable without quotes whereas if it's a guid I have to include the quotes. What is the easiest way to do make this work? My naive approach without knowledge of cleaner syntax would be:

if TEST_VAR EQ null
- run block without quotes
ELSE
- run block with quotes

But this is a contrived example, the actual use case involves 6 more parameters that I'm passing, most of which follow this guid or null scenario. So that solution wouldn't really hold up.

Thoughts on how I can do this? I'm assuming I'm just unaware of the proper syntax.

Thanks!

This message has been edited. Last edited by: FP Mod Chuck,


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
Assuming you are getting null passed through, a small bit of DM will fix this.

-SET &MyTestVar = IF &&TEST_VAR EQ 'null' THEN &&TEST_VAR ELSE '''' | &&TEST_VAR | '''' ;

ENGINE SQLMSS SET DEFAULT_CONNECTION CONN_DEV
SQL SQLMSS
EX getPermissions '&&USER_ID', &MyTestVar;
TABLE FILE SQLOUT


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6119 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Worked like a charm, thanks Waz!


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Handling potential nulls in stored procedure calls

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.