Stored Procedures are executable scripts used with database applications such as MS SQL Server and Oracle to perform complex operations. Often, a Stored Procedure is the most efficient and effective method of manipulating data in complex databases in preparation for supplying it to external applications like NWA Quality Analyst.

NWA supports the use of Stored Procedures in database connection definitions, and can also supply values to those scripts that require arguments (such as a date range or product code). Stored Procedures are considered fairly advanced functions, and are referenced in the Quality Analyst connection definition via SQL.

Stored Procedures used by Quality Analyst must return data as a “Record Set” that matches the Quality Analyst data format. The Quality Analyst connection definition process is the same as when using SQL queries. See Chapter 12 of the Quality Analyst User’s Manual for more information.

The general syntax for calling a Stored Procedure in Quality Analyst using SQL is:

        EXECUTE ‘procedure name’

Where ‘procedure name’ is the name of the stored procedure to execute. This is entered in the “SQL” window of the Quality Analyst External Database Setup form.

Using Arguments in Stored Procedures

Arguments follow the Stored Procedure name in the SQL statement. They are typically not specifically identified and must be in the correct order. Note that the actual syntax used will be dictated by the Stored Procedure script and the host database (SQL is a somewhat “non-standard” standard).

In the following example, the Stored Procedure “GetData” includes a start date for retrieving data. It is placed following the Stored Procedure name.

        EXECUTE GetData ‘3/1/06’

If the GetData procedure also required an end date and lot number, the SQL might look like this:

        EXECUTE GetData ‘3/1/06’ ‘5/30/06’ ‘A01-4453-01’

Using Replaceable Parameters

Quality Analyst also supports the use of “replaceable parameters” in SQL statements. When a parameter in an SQL statement is preceded by the caret “^” character, Quality Analyst will prompt the user for the value. Using replaceable parameters for the arguments from the previous Stored Procedure would look like:

        EXECUTE GetData ^AskBegDate ^AskEndDate ^AskLotCode

This would appear in the External Database Setup form as:

When this procedure executes, Quality Analyst will prompt for the beginning and ending dates and the Lot Code. If this Data Set is used in a Run File or to supply charts for Quality Analyst Web Server, the parameters can be passed in by prompting in the Run File or adding some prompts to the web page. See the discussions on Replaceable Parameters in Chapters 9 and 12 of the Quality Analyst User’s Manual for more details.