Sidebar

Using a Stored Procedure with Input Parameters in QIE?

0 votes
400 views
asked Jul 20, 2021 by rich-c-2789 (16,180 points)
How can I call stored procedures in QIE that have input parameters?

2 Answers

0 votes
 
Best answer

Another type of stored procedure that can be called is one that contains one or more IN parameters, which are parameters that can be used to pass data into the stored procedure.

When you create the pQuery object with the stored procedure "CALL Escape Sequence" specify the IN parameters by creating a named parameter that starts with either a : or @ character. For example ":employeeId". This acts as a placeholder for the parameter values that will be passed into the stored procedure. To specify a value for a parameter, you can use one of the setter methods of the pQuery object. The setter method that you can use is determined by the data type of the IN parameter. In this case the stored procedure expects an int so we use the setInt method to set the value.

When you pass a value to the setter method, you must specify not only the actual value that will be used in the parameter, but also the name of the named parameter used in the qie.getParameterizedQuery() method.

As an example of how to call a stored procedure that contains an IN parameter, use the uspGetEmployeeManagers stored procedure in the AdventureWorks2017 database. This stored procedure accepts a single input parameter named EmployeeID, which is an integer value, and it returns a recursive list of employees and their managers based on the specified EmployeeID. The QIE code for calling this stored procedure is as follows:

To call a stored procedure in QIE using the "CALL escape sequence" syntax, first replace the curly braces with there "numeric character sequence" equivalents. See How to escape curly braces when calling stored procedures? https://www.qvera.com/kb/index.php/2702/how-to-escape-curly-braces-when-calling-stored-procedures?

//Create the pQuery object with the stored procedure to be called
var pQuery = qie.getParameterizedQuery("{ CALL [dbo].[uspGetEmployeeManagers](:employeeId) }");

// Set the values for the input parameters
pQuery.setInt("employeeId", qie.evaluateTemplate("10"));

// Execute the stored procedure
var queryResult = pQuery.doQuery(
   "AdventureWorks2017"
);

// Get the results
message.setNode("/", queryResult.getNode("/"));

Note: Since we use evaluateTemplate in the set method above we could replace "10" with a node tag to be evaluated.  (see What is a nodetag?

See Recommendations calling MSSQL stored procedures without SET NOCOUNT ON?

This questions was inspired and adapted to QIE from this article:

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-input-parameters?view=sql-server-2017

Addition examples can be found in this question:

How to call a stored procedures in QIE?

answered Jul 20, 2021 by rich-c-2789 (16,180 points)
selected Jul 20, 2021 by rich-c-2789
0 votes

This is the sample stored procedure called in this question. This one already existed in the sample AdventureWorks2017 database from microsoft.

CREATE OR ALTER PROCEDURE [dbo].[uspGetEmployeeManagers]
    @BusinessEntityID [int]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], 0 -- Get the initial Employee
        FROM [HumanResources].[Employee] e
            INNER JOIN [Person].[Person] as p
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
        WHERE e.[BusinessEntityID] = @BusinessEntityID
        UNION ALL
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e
            INNER JOIN [EMP_cte]
            ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
            INNER JOIN [Person].[Person] p
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
    )
    -- Join back to Employee to return the manager name
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName],
        [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
    FROM [EMP_cte]
        INNER JOIN [HumanResources].[Employee] e
        ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
        INNER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25)
END;
GO
answered Jul 20, 2021 by rich-c-2789 (16,180 points)
...