Functions - How to use Stored Proc input/output params from SQL input binding

Question

I have a function that calls a Stored Procedure to create a record in my BD. The stored proc has one input parameter and one output parameter:

CREATE PROCEDURE dbo.CREATE_SEASON
@Name varchar(50),
@Season_ID INT OUTPUT
AS 
BEGIN
UPDATE dbo.Seasons SET Is_Active = 0 WHERE Seasons.Is_Active = 1;
INSERT INTO dbo.Seasons (NAME, IS_ACTIVE) values (@Name, 1);
SELECT @Season_ID = SCOPE_IDENTITY();
END

I've set up my Function to utilize the SQL input binding, but I'm trying to figure out how to:

1. Pass in the @Name input parameter (this example shows it automatically binding to the URL parameter). I will need to pass the input parameter from the code (retrieved from the HTTP body or calculated from logic in the Function code).
2. Retrieve the value from the @Season_ID output parameter.

Here is the code that I have so far:

import { app, HttpRequest, HttpResponseInit, InvocationContext, input } from "@azure/functions";

const sqlOptions = input.sql({
    commandText: 'dbo.CREATE_SEASON',
    commandType: 'StoredProcedure',
    connectionStringSetting: 'SqlConnectionString',
    parameters: '@Name={calculatedName},@Season_ID={seasonId}'
});

export async function CreateSeason(request: HttpRequest, context: InvocationContext): Promise<HttpResponseInit> {
    context.log(`Http function processed request for url "${request.url}"`);

    const name = request.query.get('name') || await request.text() || 'world';
    // calculatedName is the value that I need passed into the stored proc
    const calculatedName = name + '_Temp';

    // seasonId is the value that I need returned from the stored proc output variable
    let seasonId: number;

    context.extraInputs.get(sqlOptions);

    return { body: JSON.stringify({ seasonId }) };
};

app.http('CreateSeason', {
    methods: ['POST'],
    route: 'seasons',
    extraInputs: [sqlOptions],
    authLevel: 'anonymous',
    handler: CreateSeason
});

Currently, I get the error System.Private.CoreLib: Exception while executing function: Functions.CreateSeason. Microsoft.Azure.WebJobs.Host: No value for named parameter 'calculatedName'., but if I change "calculatedName" to "name" in the parameters option (parameters: '@Name={name},@Season_ID={seasonId}), it moves on to an error for seasonId. This makes me think that it's automatically trying to bind the "calculatedName" property to the queryParams, but it's hard to tell (I can't find documentation on it).

Answer 1

Hi @Elliot Stoner Greetings! Welcome to Microsoft Q&A forum. Thank you for posting this question here.I haven't tried the http call method, but I was able to get the output from the stored procedure using pyodbc driver.

Your understanding on the following This makes me think that it's automatically trying to bind the "calculatedName" property to the queryParams is correct. The values for the parameters are loaded before the function app gets to the following lines of code. I believe this is done from the binding extraInputs: [sqlInput] before the http trigger function gets invoked.

Here is my Stored procedure in SQL

CREATE or Alter PROCEDURE SearchName
    @Name NVARCHAR(100),
    @MatchedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @MatchedCount = COUNT(*)
    FROM Test
    WHERE Name = @Name ;
	Return @MatchedCount
END;

My Python function app for fetching the result

import { app, HttpRequest, HttpResponseInit, InvocationContext, input } from "@azure/functions";

const sqlInput = input.sql({
    commandText: `EXEC [dbo].[SearchName] @Name, @MatchedCount OUTPUT; SELECT @MatchedCount as N'@MatchedCount';`,
    commandType: 'Text',
    parameters: '@Name={Query.name},@MatchedCount={Query.matchedCount}',
    connectionStringSetting: 'sqlconnectionstring',
});

export async function httpTriggerTypeScript(request: HttpRequest, context: InvocationContext): Promise<HttpResponseInit> {
    context.log(`Http function processed request for url "${request.url}"`);

    const name = request.query.get('name') || await request.text() || 'world';

    let matchedCount: number = -1;


    try {
        const result = await context.extraInputs.get(sqlInput);
        matchedCount = result[0]['@MatchedCount'];
        context.log(`Stored procedure returned matchedCount: ${matchedCount}`);
        return {
            body: `Matched count is ${matchedCount}`
        };
    } catch (err) {
        context.log('Error fetching data from SQL', err.message);
        return {
            status: 500,
            body: `Internal Server Error: ${err.message}`
        };
    }
};

app.http('httpTriggerTypeScript', {
    methods: ['GET', 'POST'],
    authLevel: 'anonymous',
    extraInputs: [sqlInput],
    handler: httpTriggerTypeScript
});

Hope this helps! Please let us know if you have any questions or need further assistance.

Update

If there is a need to pass the parameters to the SQL stored procedure from inside the http function handler, you would have to leverage the mssql package as the binding for the parameters of input is done through extraInputs before entering the function. Please find the below sample code to use mssql

import { app, HttpRequest, HttpResponseInit, InvocationContext } from "@azure/functions";
import * as sql from 'mssql';

const sqlConfig = {
    user: '<userName>',
    password: '<password>',
    server: '<serverName>.database.windows.net',
    database: '<db_name>',
    options: {
        encrypt: true,
        trustServerCertificate: false
    }
};

export async function httpTriggerTypeScript(request: HttpRequest, context: InvocationContext): Promise<HttpResponseInit> {
    context.log(`Http function processed request for url "${request.url}"`);

    const name = request.query.get('name') || await request.text() || 'Rajesh';
    let matchedCount: number = -1;

    try {
        // Connect to the database
        await sql.connect(sqlConfig);

        // Create a new request
        const request = new sql.Request();
        request.input('Name', sql.NVarChar, name);
        request.output('MatchedCount', sql.Int);

        // Execute the stored procedure
        const result = await request.execute('dbo.SearchName');

        // Retrieve the output parameter value
        matchedCount = result.output.MatchedCount;
        context.log(`Stored procedure returned matchedCount: ${matchedCount}`);

        return {
            body: `Matched count is ${matchedCount}`
        };
    } catch (err) {
        context.log('Error fetching data from SQL', err.message);
        return {
            status: 500,
            body: `Internal Server Error: ${err.message}`
        };
    } finally {
        // Close the database connection
        await sql.close();
    }
};

app.http('httpTriggerTypeScript', {
    methods: ['GET', 'POST'],
    authLevel: 'anonymous',
    handler: httpTriggerTypeScript
});

---

If the response helped, please do click Accept Answer and Yes for the answer provided. Doing so would help other community members with similar issue identify the solution. I highly appreciate your contribution to the community.