Designing SQL Procedures and Functions

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Procedures and functions, whether written in SQL or in traditional application development languages, provide powerful tools for accessing your database.


One of the most difficult tasks when you're designing an application is deciding how to code each portion of the application. We can develop applications in CL, RPGLE, SQL, and a host of other languages. The capabilities of these languages overlap one another to a certain degree, so the decision of which language to use is not always clear-cut.


In development on the IBM i, SQL is one of the new kids on the block. As a collective whole, we developers aren't as comfortable with it and its role as we are with more traditional languages, such as CL and RPGLE. As developers become comfortable with the basics of using SQL within their applications, a natural extension of that is to begin to use stored procedures and functions. These programming tools provide interesting options for developers that allow us to more easily accomplish some of the tasks we have within our applications.


This article focuses on how to use functions and procedures and explains some design considerations, such as static versus dynamic SQL statements, determinism, SQL versus RPGLE coding, as well as testing and performance measuring.

When to Use a Stored Procedure

Stored procedures are simply programs. They can be written in SQL or other high-level languages such as RPGLE. These procedures provide the means to write code one time and then reuse it again later. The same types of activities that warranted being coded into their own programs or modules in traditional applications may also be viable candidates for stored procedures.


For example, you might choose to create a stored procedure that is responsible for adding a PO Header record to the database. The stored procedure could receive all the necessary data as parameters, edit them, update the table, and perform additional tasks such as logging data to a history file. This gives you a single point of control for all the work related to adding a record to that table. You might also choose to create a stored procedure to retrieve data, such as all of the open POs in the PO Header table. Any filters, security tasks, or reformatting options required can be carried out by the stored procedure.


Another reason for using stored procedures is that they can improve performance and provide additional security options, such as adopted authority. Since stored procedures are compiled, some of the work of analyzing their SQL statements is completed at compile time, allowing that step to be skipped at run time, which can lead to improved performance. High-level language programs can be compiled with adopted authority (set the compile option to USER(*OWNER)). This allows the stored procedure to run using the permissions of the programmer who compiled it rather than the user who is executing it. Using this feature, developers may provide user IDs with very limited rights to programmers who need remote connectivity. Rather than accessing the data directly, they use these limited profiles to access stored procedures, which adopt a more trusted profile and perform the necessary work.


One of the issues we often struggle with when using a stored procedure is how the SQL engine locates the stored procedure. First of all, the logic that is used for this depends upon the naming convention in use for this connection to the database. If you are using the traditional system naming convention of library/file, then the SQL engine will use the current user's library list to find stored procedures. If you are using an SQL naming convention of library.file, the library list is the system libraries and a library is named the same as the current user.

Stored Procedure Language

Which language is the right language to code in? That depends on a number of factors. First, is the task being performed something you already have code for? If you already have RPGLE code that accomplishes the necessary task, then it's relatively easy to register that program or a modified copy of that program as a stored procedure. This is great for leveraging existing code and reducing your development costs.


If you don't already have the code written, you should consider the task to be performed. If the task is to generate a report, obviously RPGLE is a better choice than SQL. However, if the task is to either retrieve or write data to a database, perhaps SQL is the better choice. For some tasks, you might even find that CLLE is the best language.

Static vs. Dynamic

Whether you are coding in SQL or RPGLE, you have to decide if your SQL statements should be static or dynamic. Static or "hard-coded" statements may outperform dynamic statements, which require additional analysis at run time. But dynamic statements offer more flexibility.


The following example shows a static SQL statement in a stored procedure. This procedure receives a five-digit decimal customer number and then returns all the matching records from the database.


create procedure GETCUST 1  

(in Custid dec(5 0))     

language sql     

result sets 1                          




Figure 1: Static SQL Statement


Figure 2 shows an example of a dynamic SQL statement in a stored procedure. This procedure receives in a customer number as a character string (even though customer number is numeric, passing it a character value makes the concatenation simpler). The procedure is written in SQL and returns one result set.


create procedure your-lib/getcust1                       

(in cust char(10))                                      

 language sql                                          

 result sets 1                                 


 declare stmt char(50);                                

 declare c1 cursor for s1;                             

 set stmt = 'SELECT * FROM CUST where cnumber = '  concat cust;

 prepare s1 from stmt;                                 

 open c1;                                               



Figure 2: Dynamic SQL Statement

Result Sets

As seen in the previous examples, stored procedures can return one or more result sets to their caller. But RPGLE programs cannot receive result sets. Therefore, RPGLE programs cannot call stored procedures that return result sets. Even though RPGLE programs cannot receive result sets, that does not prevent them from returning result sets if they are defined as stored procedures.


Figure 3 shows the code to use when returning a multiple-occurrence data structure as a result set. In this case, the variable X contains a count of how many rows in the APTMNTS data structure to return.


C/EXEC SQL                                                     

C+         SET RESULT SETS ARRAY :Aptmnts  FOR :X Rows        


Figure 3: RPGLE Embedded SQL to Return a Multiple-Occurrence Data Structure


That works well for traditional legacy programs that are already building a set of data for a report or a subfile program that we now want to return as a result set. But if we are working with a request for new data, we might prefer to follow the example shown in Figure 4 and open a cursor in the RPGLE program and return that result set to the call.


C/EXEC SQL                                     




C/EXEC SQL                                     

C+    OPEN C1                                  



C/EXEC SQL                                      

C+         SET RESULT SETS CURSOR C1           


Figure 4: RPGLE Embedded SQL to Return a Result Set


Stored procedures do not need to return result sets. They can simply pass parameters back and forth if needed or, even more simply, just perform a specific task, like initializing all the tables in a database or deleting and recreating all the indexes for a database.

When to Use a Function

Functions are similar to procedures in that they are code routines (written in either SQL or some other high-level language) that perform a specific task. They differ from procedures in that they are called from within an SQL statement, usually taking in one or more arguments and returning a related value.


In general, functions will tend to perform a smaller, more focused task than a procedure. For example, you might use a function to convert a date stored in a legacy format into a real date data type. You might also use a function to perform some sort of translation, giving a value as an argument and receiving back the related value, such as an item price lookup. Figure 5 shows a function that receives an order number as an argument and returns the one-byte character value of its status.







Declare FLAG Char(1);

Set FLAG = '*';




Figure 5: SQL Function to Return a Status Flag


Anytime you have data to convert from the format it's stored in within the database into a more usable format, coding your own function is an option. If the available SQL functions cannot easily perform the conversion, then coding your own is a reasonable solution.


If you already have code written in a language such as RPGLE that performs the necessary work, then registering a subprocedure as a function may be the simplest way to deploy that solution. Otherwise, you might choose to create a new function in SQL.

Deterministic Functions

If you define a function as deterministic, you are authorizing the SQL engine to use cached results of previous calls to the function rather than call it each time. For example, the SQL function UPPER('a') will always translate the lowercase a into an uppercase A. That is deterministic. If that function is used in a result set that returns 1000 rows, all with a lowercase a, then the SQL can call the function one time, and the other 999, it can pull the result from its cache, improving performance.


If you are sure your function is deterministic, then code it that way to gain performance whenever possible. If you are not sure, assume it's not deterministic, as that will prevent the SQL engine from returning incorrect data.

Overloading Functions

Functions may be overloaded, meaning that you can create many functions, all with the same name, as long as each function has a different set of parameters. At run time, the SQL engine will determine which of the functions to invoke, based upon the parameters passed.


Figure 6 shows CREATE statements for two functions that share the same name. The specific name parameter may be used to identify the specific version of the function.







Declare FLAG Char(1);

Set FLAG = '*';









Declare FLAG Char(1);

Set FLAG = '*';




 Figure 6: Overloaded Function


This very useful option allows us to configure a function to receive decimal, numeric, integer, or other numeric data types without forcing the users to know which version of the function to call.


Typically, functions process only the data passed to them as arguments and do not store any information from previous executions. However, if you need a function to retain data between each row it's called for, you can use a SCRATCHPAD option in the CREATE command. Coding a scratchpad function is too complex to cover in detail here.


Testing stored procedure calls from the STRSQL command is not feasible as it cannot handle parameters and result sets. So the simplest way to test a stored procedure is to use the RUN SQL SCRIPTS tool, which is distributed for free within iSeries Navigator.


With this tool, you can test the functioning of the stored procedure by calling it with various parameters and evaluating its results. You also have the option to use the visual explain tool to analyze the performance of an SQL command and measure its performance.


The database feature of iSeries Navigator also contains an SQL monitor tool, which allows administrators to watch the execution of SQL statements for virtually any job on the system, including the execution of programs that you do not have source code for.

Feel the Power

Procedures and functions, whether they are written in SQL or use traditional application development languages such as RPGLE, provide powerful tools for accessing your database. One of the greatest benefits of these SQL based tools is that they can be used internally within your own applications, yet also provide a framework for integrating with external tools and applications through common interfaces such as ODBC and JDBC. 



Support MC Press Online





  • White Paper: Node.js for Enterprise IBM i Modernization

    SB Profound WP 5539

    If your business is thinking about modernizing your legacy IBM i (also known as AS/400 or iSeries) applications, you will want to read this white paper first!

    Download this paper and learn how Node.js can ensure that you:
    - Modernize on-time and budget - no more lengthy, costly, disruptive app rewrites!
    - Retain your IBM i systems of record
    - Find and hire new development talent
    - Integrate new Node.js applications with your existing RPG, Java, .Net, and PHP apps
    - Extend your IBM i capabilties to include Watson API, Cloud, and Internet of Things

    Read Node.js for Enterprise IBM i Modernization Now!


  • Profound Logic Solution Guide

    SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation.
    Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects.
    The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the companyare not aligned with the current IT environment.

    Get your copy of this important guide today!


  • 2022 IBM i Marketplace Survey Results

    Fortra2022 marks the eighth edition of the IBM i Marketplace Survey Results. Each year, Fortra captures data on how businesses use the IBM i platform and the IT and cybersecurity initiatives it supports.

    Over the years, this survey has become a true industry benchmark, revealing to readers the trends that are shaping and driving the market and providing insight into what the future may bring for this technology.

  • Brunswick bowls a perfect 300 with LANSA!

    FortraBrunswick is the leader in bowling products, services, and industry expertise for the development and renovation of new and existing bowling centers and mixed-use recreation facilities across the entertainment industry. However, the lifeblood of Brunswick’s capital equipment business was running on a 15-year-old software application written in Visual Basic 6 (VB6) with a SQL Server back-end. The application was at the end of its life and needed to be replaced.
    With the help of Visual LANSA, they found an easy-to-use, long-term platform that enabled their team to collaborate, innovate, and integrate with existing systems and databases within a single platform.
    Read the case study to learn how they achieved success and increased the speed of development by 30% with Visual LANSA.


  • The Power of Coding in a Low-Code Solution

    LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed.
    Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • Why Migrate When You Can Modernize?

    LANSABusiness users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
    In this white paper, you’ll learn how to think of these issues as opportunities rather than problems. We’ll explore motivations to migrate or modernize, their risks and considerations you should be aware of before embarking on a (migration or modernization) project.
    Lastly, we’ll discuss how modernizing IBM i applications with optimized business workflows, integration with other technologies and new mobile and web user interfaces will enable IT – and the business – to experience time-added value and much more.


  • UPDATED: Developer Kit: Making a Business Case for Modernization and Beyond

    Profound Logic Software, Inc.Having trouble getting management approval for modernization projects? The problem may be you're not speaking enough "business" to them.

    This Developer Kit provides you study-backed data and a ready-to-use business case template to help get your very next development project approved!

  • What to Do When Your AS/400 Talent Retires

    FortraIT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators is small.

    This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:

    • Why IBM i skills depletion is a top concern
    • How leading organizations are coping
    • Where automation will make the biggest impact


  • Node.js on IBM i Webinar Series Pt. 2: Setting Up Your Development Tools

    Profound Logic Software, Inc.Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. In Part 2, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Attend this webinar to learn:

    • Different tools to develop Node.js applications on IBM i
    • Debugging Node.js
    • The basics of Git and tools to help those new to it
    • Using as a pre-built development environment



  • Expert Tips for IBM i Security: Beyond the Basics

    SB PowerTech WC GenericIn this session, IBM i security expert Robin Tatam provides a quick recap of IBM i security basics and guides you through some advanced cybersecurity techniques that can help you take data protection to the next level. Robin will cover:

    • Reducing the risk posed by special authorities
    • Establishing object-level security
    • Overseeing user actions and data access

    Don't miss this chance to take your knowledge of IBM i security beyond the basics.



  • 5 IBM i Security Quick Wins

    SB PowerTech WC GenericIn today’s threat landscape, upper management is laser-focused on cybersecurity. You need to make progress in securing your systems—and make it fast.
    There’s no shortage of actions you could take, but what tactics will actually deliver the results you need? And how can you find a security strategy that fits your budget and time constraints?
    Join top IBM i security expert Robin Tatam as he outlines the five fastest and most impactful changes you can make to strengthen IBM i security this year.
    Your system didn’t become unsecure overnight and you won’t be able to turn it around overnight either. But quick wins are possible with IBM i security, and Robin Tatam will show you how to achieve them.

  • Security Bulletin: Malware Infection Discovered on IBM i Server!

    SB PowerTech WC GenericMalicious programs can bring entire businesses to their knees—and IBM i shops are not immune. It’s critical to grasp the true impact malware can have on IBM i and the network that connects to it. Attend this webinar to gain a thorough understanding of the relationships between:

    • Viruses, native objects, and the integrated file system (IFS)
    • Power Systems and Windows-based viruses and malware
    • PC-based anti-virus scanning versus native IBM i scanning

    There are a number of ways you can minimize your exposure to viruses. IBM i security expert Sandi Moore explains the facts, including how to ensure you're fully protected and compliant with regulations such as PCI.



  • Encryption on IBM i Simplified

    SB PowerTech WC GenericDB2 Field Procedures (FieldProcs) were introduced in IBM i 7.1 and have greatly simplified encryption, often without requiring any application changes. Now you can quickly encrypt sensitive data on the IBM i including PII, PCI, PHI data in your physical files and tables.
    Watch this webinar to learn how you can quickly implement encryption on the IBM i. During the webinar, security expert Robin Tatam will show you how to:

    • Use Field Procedures to automate encryption and decryption
    • Restrict and mask field level access by user or group
    • Meet compliance requirements with effective key management and audit trails


  • Lessons Learned from IBM i Cyber Attacks

    SB PowerTech WC GenericDespite the many options IBM has provided to protect your systems and data, many organizations still struggle to apply appropriate security controls.
    In this webinar, you'll get insight into how the criminals accessed these systems, the fallout from these attacks, and how the incidents could have been avoided by following security best practices.

    • Learn which security gaps cyber criminals love most
    • Find out how other IBM i organizations have fallen victim
    • Get the details on policies and processes you can implement to protect your organization, even when staff works from home

    You will learn the steps you can take to avoid the mistakes made in these examples, as well as other inadequate and misconfigured settings that put businesses at risk.



  • The Power of Coding in a Low-Code Solution

    SB PowerTech WC GenericWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed.
    Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • The Biggest Mistakes in IBM i Security

    SB Profound WC Generic The Biggest Mistakes in IBM i Security
    Here’s the harsh reality: cybersecurity pros have to get their jobs right every single day, while an attacker only has to succeed once to do incredible damage.
    Whether that’s thousands of exposed records, millions of dollars in fines and legal fees, or diminished share value, it’s easy to judge organizations that fall victim. IBM i enjoys an enviable reputation for security, but no system is impervious to mistakes.
    Join this webinar to learn about the biggest errors made when securing a Power Systems server.
    This knowledge is critical for ensuring integrity of your application data and preventing you from becoming the next Equifax. It’s also essential for complying with all formal regulations, including SOX, PCI, GDPR, and HIPAA
    Watch Now.

  • Comply in 5! Well, actually UNDER 5 minutes!!

    SB CYBRA PPL 5382

    TRY the one package that solves all your document design and printing challenges on all your platforms.

    Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product.

    Request your trial now!

  • Backup and Recovery on IBM i: Your Strategy for the Unexpected

    FortraRobot automates the routine tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
    - Simplified backup procedures
    - Easy data encryption
    - Save media management
    - Guided restoration
    - Seamless product integration
    Make sure your data survives when catastrophe hits. Try the Robot Backup and Recovery Solution FREE for 30 days.

  • Manage IBM i Messages by Exception with Robot

    SB HelpSystems SC 5413Managing messages on your IBM i can be more than a full-time job if you have to do it manually. How can you be sure you won’t miss important system events?
    Automate your message center with the Robot Message Management Solution. Key features include:
    - Automated message management
    - Tailored notifications and automatic escalation
    - System-wide control of your IBM i partitions
    - Two-way system notifications from your mobile device
    - Seamless product integration
    Try the Robot Message Management Solution FREE for 30 days.

  • Easiest Way to Save Money? Stop Printing IBM i Reports

    FortraRobot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing.
    Manage your reports with the Robot Report Management Solution. Key features include:

    - Automated report distribution
    - View online without delay
    - Browser interface to make notes
    - Custom retention capabilities
    - Seamless product integration
    Rerun another report? Never again. Try the Robot Report Management Solution FREE for 30 days.

  • Hassle-Free IBM i Operations around the Clock

    SB HelpSystems SC 5413For over 30 years, Robot has been a leader in systems management for IBM i.
    Manage your job schedule with the Robot Job Scheduling Solution. Key features include:
    - Automated batch, interactive, and cross-platform scheduling
    - Event-driven dependency processing
    - Centralized monitoring and reporting
    - Audit log and ready-to-use reports
    - Seamless product integration
    Scale your software, not your staff. Try the Robot Job Scheduling Solution FREE for 30 days.