From Stored Procedures to Power Apps: Safely Surfacing MS-SQL Business Logic to Non‑Developers

Scenario: Credit Limit Approvals Sitting in the Database

Imagine an internal credit team.

• All credit limit decisions are driven by a set of SQL stored procedures.
• Those procedures encapsulate rules around customer risk, outstanding balance, payment history, and manual overrides.
• The logic has been tuned over years and is audited by finance.

Today, the workflow looks like this:

1. A salesperson emails a request for a higher credit limit.
2. Someone in the credit team runs a stored procedure manually in SSMS.
3. They paste the result into an email and update a CRM system.

Now the business asks for a Power App:

• Sales should submit requests from their phone.
• Credit should approve in a simple UI.
• The existing stored procedures must remain the source of truth.

We want to:

• Keep the SQL logic where it is.
• Avoid giving Power Apps makers direct write access to the core tables.
• Make the flow self‑service and auditable.

The rest of the article walks through one way to do this safely.

Step 1: Decide How Power Apps Will Call Your SQL Logic

Power Apps does not natively execute arbitrary stored procedures via the built‑in SQL Server connector in a way that covers all scenarios (especially complex parameters and result sets). While the SQL Server connector can call stored procedures that meet its parameter and result-set constraints, it is not suitable for all patterns (for example, procedures that return multiple result sets or require complex table-valued parameters). As of current documentation, a robust and flexible way to expose stored procedures to Power Apps is:

• Wrap the stored procedure behind a web API.
• Call that API from Power Apps using a custom connector.

Other options exist (e.g. direct SQL connector queries, Power Automate flows calling SQL), but the API + custom connector pattern gives you:

• Clear separation between UI and data logic.
• Fine‑grained control over what operations are exposed.
• A stable contract (request/response) independent of your database schema.

For our credit limit scenario, we’ll build:

• A POST /credit/assess endpoint that calls usp_AssessCreditLimit.
• A Power Apps custom connector that exposes this endpoint to makers.

Step 2: Shape Stored Procedure Inputs and Outputs for an API

APIs speak JSON; stored procedures speak T‑SQL. You need a clean mapping between the two.

Stored procedure design

You don’t have to rewrite your business logic, but you may need a wrapper procedure that:

• Accepts a well‑defined set of parameters.
• Returns a single row or a small result set.
• Uses clear column names that can be mapped directly to JSON fields.

Example wrapper for our scenario:

CREATE PROCEDURE dbo.usp_AssessCreditLimit
    @CustomerId           INT,
    @RequestedLimit       DECIMAL(18, 2),
    @RequestedByUserId    INT,
    @RequestTimestampUtc  DATETIME2(0)
AS
BEGIN
    SET NOCOUNT ON;

    -- Example: call existing complex logic
    -- Assume dbo.usp_CalculateCreditScore already exists
    DECLARE @CreditScore INT;

    EXEC dbo.usp_CalculateCreditScore
        @CustomerId = @CustomerId,
        @CreditScore = @CreditScore OUTPUT;

    DECLARE @CurrentBalance DECIMAL(18, 2);
    DECLARE @ApprovedLimit DECIMAL(18, 2);
    DECLARE @DecisionCode  NVARCHAR(50);

    -- Business logic simplified for illustration
    SELECT @CurrentBalance = CurrentBalance
    FROM dbo.CustomerAccounts
    WHERE CustomerId = @CustomerId;

    IF @CreditScore < 500
    BEGIN
        SET @ApprovedLimit = 0;
        SET @DecisionCode  = N'Rejected_Risk';
    END
    ELSE IF @RequestedLimit > 100000
    BEGIN
        SET @ApprovedLimit = 100000;
        SET @DecisionCode  = N'Requires_Manual_Review';
    END
    ELSE
    BEGIN
        SET @ApprovedLimit = @RequestedLimit;
        SET @DecisionCode  = N'Approved_Auto';
    END

    SELECT
        @CustomerId          AS CustomerId,
        @RequestedLimit      AS RequestedLimit,
        @CurrentBalance      AS CurrentBalance,
        @CreditScore         AS CreditScore,
        @ApprovedLimit       AS ApprovedLimit,
        @DecisionCode        AS DecisionCode,
        @RequestedByUserId   AS RequestedByUserId,
        @RequestTimestampUtc AS RequestTimestampUtc;
END;
GO

Key points:

• SET NOCOUNT ON; avoids extra rowcount messages that can confuse some client libraries.
• The procedure returns a single row with all fields needed by the Power App.
• The heavy logic remains in usp_CalculateCreditScore and other internal procedures.

Step 3: Build a Thin Web API Over SQL

You can use any stack that supports HTTP + SQL Server. A common choice is ASP.NET Core.

The API’s job:

• Validate incoming JSON.
• Call the stored procedure with parameters.
• Map the result set to a JSON response.
• Enforce security (authentication, authorization).

Example controller (simplified, but runnable in a typical ASP.NET Core project):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;

namespace CreditService.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class CreditController : ControllerBase
    {
        private readonly string _connectionString;

        public CreditController(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("CreditDb");
        }

        public class CreditAssessmentRequest
        {
            public int CustomerId { get; set; }
            public decimal RequestedLimit { get; set; }
            public int RequestedByUserId { get; set; }
            public DateTime RequestTimestampUtc { get; set; }
        }

        public class CreditAssessmentResponse
        {
            public int CustomerId { get; set; }
            public decimal RequestedLimit { get; set; }
            public decimal CurrentBalance { get; set; }
            public int CreditScore { get; set; }
            public decimal ApprovedLimit { get; set; }
            public string DecisionCode { get; set; }
            public int RequestedByUserId { get; set; }
            public DateTime RequestTimestampUtc { get; set; }
        }

        [HttpPost("assess")]
        public async Task<ActionResult<CreditAssessmentResponse>> Assess([FromBody] CreditAssessmentRequest request)
        {
            if (request == null)
            {
                return BadRequest("Request body is required.");
            }

            using (var connection = new SqlConnection(_connectionString))
            using (var command = new SqlCommand("dbo.usp_AssessCreditLimit", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@CustomerId", request.CustomerId);
                command.Parameters.AddWithValue("@RequestedLimit", request.RequestedLimit);
                command.Parameters.AddWithValue("@RequestedByUserId", request.RequestedByUserId);
                command.Parameters.AddWithValue("@RequestTimestampUtc", request.RequestTimestampUtc);

                await connection.OpenAsync();

                using (var reader = await command.ExecuteReaderAsync())
                {
                    if (!await reader.ReadAsync())
                    {
                        return NotFound("No assessment result returned.");
                    }

                    var response = new CreditAssessmentResponse
                    {
                        CustomerId = reader.GetInt32(reader.GetOrdinal("CustomerId")),
                        RequestedLimit = reader.GetDecimal(reader.GetOrdinal("RequestedLimit")),
                        CurrentBalance = reader.GetDecimal(reader.GetOrdinal("CurrentBalance")),
                        CreditScore = reader.GetInt32(reader.GetOrdinal("CreditScore")),
                        ApprovedLimit = reader.GetDecimal(reader.GetOrdinal("ApprovedLimit")),
                        DecisionCode = reader.GetString(reader.GetOrdinal("DecisionCode")),
                        RequestedByUserId = reader.GetInt32(reader.GetOrdinal("RequestedByUserId")),
                        RequestTimestampUtc = reader.GetDateTime(reader.GetOrdinal("RequestTimestampUtc"))
                    };

                    return Ok(response);
                }
            }
        }
    }
}

This controller:

• Exposes POST /api/credit/assess.
• Calls the stored procedure with the incoming parameters.
• Returns a single JSON object that Power Apps can consume.

Security considerations:

• Use a dedicated SQL login with least privilege (execute on wrapper procedures only).
• Put the API behind Azure API Management if you need throttling, logging, and central policy control.
• Use Azure AD authentication so Power Apps can call it via a secure custom connector.

Step 4: Create a Custom Connector in Power Apps

Once the API is live, you define a custom connector so makers can use it like any other data source.

At a high level:

1. In Power Apps (or Power Platform admin center), create a custom connector.
2. Choose OpenAPI definition or HTTPS + manual definition.
3. Define:
   • Base URL (e.g. https://credit-api.contoso.internal/api).
   • Security (typically OAuth 2.0 with Azure AD).
   • The assess action: method POST, path /credit/assess, request/response schema.

Example OpenAPI fragment (simplified):

{
  "openapi": "3.0.1",
  "info": {
    "title": "Credit Assessment API",
    "version": "1.0"
  },
  "paths": {
    "/credit/assess": {
      "post": {
        "summary": "Assess requested credit limit",
        "operationId": "AssessCreditLimit",
        "requestBody": {
          "required": true,
          "content": {
            "application/json": {
              "schema": {
                "$ref": "#/components/schemas/CreditAssessmentRequest"
              }
            }
          }
        },
        "responses": {
          "200": {
            "description": "Assessment result",
            "content": {
              "application/json": {
                "schema": {
                  "$ref": "#/components/schemas/CreditAssessmentResponse"
                }
              }
            }
          }
        }
      }
    }
  },
  "components": {
    "schemas": {
      "CreditAssessmentRequest": {
        "type": "object",
        "properties": {
          "CustomerId": { "type": "integer" },
          "RequestedLimit": { "type": "number", "format": "double" },
          "RequestedByUserId": { "type": "integer" },
          "RequestTimestampUtc": { "type": "string", "format": "date-time" }
        },
        "required": ["CustomerId", "RequestedLimit", "RequestedByUserId", "RequestTimestampUtc"]
      },
      "CreditAssessmentResponse": {
        "type": "object",
        "properties": {
          "CustomerId": { "type": "integer" },
          "RequestedLimit": { "type": "number", "format": "double" },
          "CurrentBalance": { "type": "number", "format": "double" },
          "CreditScore": { "type": "integer" },
          "ApprovedLimit": { "type": "number", "format": "double" },
          "DecisionCode": { "type": "string" },
          "RequestedByUserId": { "type": "integer" },
          "RequestTimestampUtc": { "type": "string", "format": "date-time" }
        }
      }
    }
  }
}

Once imported and configured, the connector appears in Power Apps with an action like CreditAssessmentAPI.AssessCreditLimit.

Step 5: Call the Stored Procedure From Power Apps

With the custom connector ready, the maker can wire it into the app.

In our scenario, the app has:

• A dropdown ddlCustomer for selecting the customer.
• A text input txtRequestedLimit for entering the new limit.
• A button btnSubmit to run the assessment.
• A label lblDecision to show the decision.

Example Power Apps formula for the button’s OnSelect:

Set(
    varAssessment,
    CreditAssessmentAPI.AssessCreditLimit(
        {
            CustomerId: Value(ddlCustomer.Selected.CustomerId),
            RequestedLimit: Value(txtRequestedLimit.Text),
            RequestedByUserId: 0, // map to a numeric ID from your identity system
            RequestTimestampUtc: Now()
        }
    )
);

// Show the decision code in a label
Set(
    varDecisionText,
    "Decision: " & varAssessment.DecisionCode & 
    " | Approved limit: " & Text(varAssessment.ApprovedLimit, "#,##0")
);

Notify("Assessment completed.", NotificationType.Success);

Notes:

• CreditAssessmentAPI.AssessCreditLimit returns a record shaped like CreditAssessmentResponse.
• The maker never sees the database or stored procedure; they work with a typed connector.
• You can store varAssessment in a Dataverse table or another system for audit.

This is the after picture for our credit team: requests are submitted and assessed in the app, but the decision logic remains in SQL.

Step 6: Security and Governance – Avoiding the Common Traps

Exposing stored procedures to non‑developers is mostly a security and governance problem, not a technical one.

Focus on these areas:

1. Least privilege on SQL

• Use a dedicated SQL login for the API.
• Grant EXECUTE only on wrapper procedures (usp_AssessCreditLimit, etc.).
• Do not grant direct SELECT/INSERT/UPDATE on business tables unless the procedure requires it and you can’t encapsulate it.

2. Control what the API exposes

• Only expose endpoints that represent business operations (e.g. "assess credit", "create order"), not low‑level data access.
• Validate input in the API (ranges, required fields) before calling SQL.
• Return only the fields the app needs; avoid leaking internal flags or IDs.

3. Authentication and authorization

• Use Azure AD auth for the API and custom connector.
• Map user identity into the stored procedure parameters when needed (e.g. RequestedByUserId).
• Apply role‑based checks in the API if certain operations should only be available to specific groups.

4. Versioning and change management

Stored procedures evolve. If you change parameters or result sets without coordinating with the API and connector, apps will break.

Practical pattern:

• Treat wrapper procedures and API endpoints as versioned contracts.
• When you need to change behaviour, add a new procedure and endpoint (e.g. usp_AssessCreditLimit_v2, /credit/assess-v2).
• Migrate apps to the new connector action, then retire the old version.

Step 7: When to Use Power Automate Instead of Direct API Calls

Sometimes it’s cleaner to call stored procedures indirectly via Power Automate:

• The Flow uses the SQL Server connector to run a stored procedure or query.
• Power Apps calls the Flow action instead of the API.

This can be useful when:

• You want to chain multiple operations (call several procedures, send email, update Dataverse) in one transaction-like flow.
• Makers are more comfortable editing Flows than working with an external API.

However:

• You still need to be careful with connection permissions for the SQL connector.
• Long‑running or high‑volume operations may hit Flow limits; APIs scale better for heavy workloads.

For our credit scenario:

• Use Power Automate if you want a Flow that assesses credit, logs the result, and sends notifications.
• Use the direct API + custom connector path if you want low latency and more control over error handling.

Step 8: Testing and Observability – Prove the Logic is the Same

The business will ask: "How do we know the Power App is using the same rules as the old SSMS process?"

You need:

1. Baseline tests

   • Prepare a set of test customers and requested limits.
   • Run the stored procedure directly in SSMS.
   • Run the same cases through the API and Power App.
   • Compare results (decision code, approved limit, score) to confirm parity.

2. Logging

   • Log incoming requests and procedure outputs in the API.
   • Include user identity and timestamp.
   • Use those logs to troubleshoot mismatches and support audits.

3. Monitoring

   • Track API response times and error rates.
   • Watch for timeouts if procedures are slow.
   • Tune indexes and procedure logic as needed; the app shouldn’t have to compensate for slow SQL.

In our credit team scenario, this is where you convince stakeholders that moving from SSMS to Power Apps hasn’t changed the decision engine, only the interface.

Practical Takeaway: Wrap Your Stored Procedures Before You Expose Them

If you remember one thing from this pattern, make it this:

Don’t point Power Apps directly at your core tables or complex stored procedures; wrap them behind a stable, minimal API or Flow contract, then expose that via a custom connector.

It keeps the business logic in MS‑SQL where it belongs, gives non‑developers a safe way to use it, and lets you evolve your database without breaking every app that depends on it.