How to call PaaS SQL server stored procedure from Azure Functions in Azure

To Call a SQL stored procedure from the Azure Function using the below steps:

• Log in to the Azure Portal
• Create an Azure Function App in Visual Studio 2019 and use the SQL Database connection string from Azure Portal, SQLConnection, and SQLCommand object to call the Stored Procedure.

Essentially the code to call/exec the SP would be something like:

using System.Net;
using System.Data;
using System.Data.SqlClient;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info("C# HTTP trigger function stored procedure call request.");

    dynamic body = await req.Content.ReadAsStringAsync();
    
    //Connect to SQL
    var cnnString  = "XXXXX";
        
    using (SqlConnection conn = new SqlConnection(cnnString))
    {
        conn.Open();
        
        SqlCommand cmd = new SqlCommand("YourSPnameHere", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        
        var reader = cmd.ExecuteReader();
    }

    return req.CreateResponse(HttpStatusCode.OK, "Ok");
}

Select allOpen in new window

For more details/screenshots please have a look at this link: https://azurelessons.com/call-a-stored-procedure-from-azure-functions/