SQL Server

SQL Server Stored Procedures for Access Developers

Stored procedures are one of the biggest advantages SQL Server has over Access. Here is what they are, why they matter, and how to start writing them as an Access developer.

A
Anthony Passeri, MBA
••6 min read
SQL Server Stored Procedures for Access Developers

When Access developers migrate to SQL Server, stored procedures are often the first thing that surprises them — in a good way.

In Access, your query logic lives in saved queries or embedded SQL strings in VBA. In SQL Server, stored procedures let you move that logic to the database server, where it runs faster, stays secure, and can be reused by any application that connects to the database.

This post explains what stored procedures are, why they matter, and how to write your first ones as someone coming from an Access background.

What Is a Stored Procedure?

A stored procedure is a named, saved block of T-SQL code that lives in the database. You call it by name, optionally pass parameters, and it executes on the server.

Think of it like a saved query in Access — but more powerful. Stored procedures can:

  • Accept input parameters
  • Return result sets, output parameters, or return codes
  • Contain conditional logic (IF/ELSE)
  • Execute multiple statements in sequence
  • Handle errors with TRY/CATCH
  • Call other stored procedures

Why Stored Procedures Beat Inline SQL

Performance. SQL Server compiles and caches the execution plan for a stored procedure the first time it runs. Subsequent calls reuse the cached plan, which is faster than parsing and compiling a new SQL string every time.

Security. You can grant users permission to execute a stored procedure without giving them direct access to the underlying tables. A user can run EXEC GetCustomerOrders @CustomerID = 42 without being able to run SELECT * FROM Orders directly.

Maintainability. Business logic in a stored procedure is in one place. If the logic changes, you update the procedure — not every form, report, and VBA module that embeds the same SQL string.

Reduced network traffic. Instead of sending a long SQL string from your application to the server, you send a short EXEC ProcedureName @Param = value. The server does the work.

Your First Stored Procedure

Here is a simple stored procedure that retrieves orders for a customer:

CREATE PROCEDURE GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Orders
    WHERE CustomerID = @CustomerID
    ORDER BY OrderDate DESC;
END;

To run it in SQL Server Management Studio:

EXEC GetCustomerOrders @CustomerID = 42;

That is it. The procedure accepts a customer ID, queries the Orders table, and returns the matching rows.

SET NOCOUNT ON suppresses the "X rows affected" message that SQL Server normally returns. It is a best practice for stored procedures — it reduces network traffic and prevents issues with some client libraries.

Parameters: Input, Output, and Default Values

Input parameters (like @CustomerID above) pass values into the procedure.

Output parameters pass values back to the caller:

CREATE PROCEDURE GetOrderCount
    @CustomerID INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @OrderCount = COUNT(*)
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

Call it like this:

DECLARE @Count INT;
EXEC GetOrderCount @CustomerID = 42, @OrderCount = @Count OUTPUT;
SELECT @Count;

Default parameter values make parameters optional:

CREATE PROCEDURE GetRecentOrders
    @DaysBack INT = 30
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE OrderDate >= DATEADD(day, -@DaysBack, GETDATE())
    ORDER BY OrderDate DESC;
END;

Call with the default: EXEC GetRecentOrders; Call with a custom value: EXEC GetRecentOrders @DaysBack = 90;

Error Handling with TRY/CATCH

Access VBA has On Error GoTo. SQL Server has TRY/CATCH:

CREATE PROCEDURE InsertOrder
    @CustomerID INT,
    @TotalAmount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, Status)
        VALUES (@CustomerID, GETDATE(), @TotalAmount, 'Pending');

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        -- Re-raise the error to the caller
        THROW;
    END CATCH;
END;

The BEGIN TRANSACTION / COMMIT / ROLLBACK pattern ensures that if anything goes wrong during the insert, the partial change is rolled back. This is something Access cannot do reliably with multi-table updates.

Calling Stored Procedures from Access VBA

If you are using linked tables to SQL Server (the common migration path), you can call stored procedures from Access VBA using pass-through queries or ADO:

Using a pass-through query:

  1. Create a new query in Access
  2. Go to Query > Pass-Through
  3. Set the ODBC connection string
  4. Enter: EXEC GetCustomerOrders @CustomerID = 42

Using ADO in VBA:

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Server=YourServer;Database=YourDB;Trusted_Connection=Yes;"

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "GetCustomerOrders"
cmd.Parameters.Append cmd.CreateParameter("@CustomerID", adInteger, adParamInput, , 42)

Set rs = cmd.Execute

' Use the recordset
Do While Not rs.EOF
    Debug.Print rs!OrderID & " - " & rs!OrderDate
    rs.MoveNext
Loop

rs.Close
conn.Close

Calling Stored Procedures from Power Apps

If you are migrating to Power Apps with a SQL Server backend, you can call stored procedures through Power Automate flows or directly via the SQL Server connector.

In Power Automate: use the "Execute stored procedure" action from the SQL Server connector. Pass parameters, get results back as a JSON array.

In Power Apps: use Power Automate as the bridge — Power Apps calls a flow, the flow calls the stored procedure, the flow returns the result to Power Apps.

Modifying and Dropping Procedures

To update an existing procedure:

ALTER PROCEDURE GetCustomerOrders
    @CustomerID INT,
    @Status VARCHAR(20) = NULL  -- added optional parameter
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderID, OrderDate, TotalAmount, Status
    FROM Orders
    WHERE CustomerID = @CustomerID
      AND (@Status IS NULL OR Status = @Status)
    ORDER BY OrderDate DESC;
END;

To remove a procedure:

DROP PROCEDURE GetCustomerOrders;

Naming Conventions

A few conventions that make SQL Server databases easier to navigate:

  • Prefix with usp_ (user stored procedure) to distinguish from system procedures: usp_GetCustomerOrders
  • Or use a schema prefix: dbo.GetCustomerOrders
  • Use verb-noun naming: GetOrders, InsertCustomer, UpdateOrderStatus, DeleteExpiredSessions
  • Avoid the sp_ prefix — SQL Server reserves it for system procedures and searches the master database first, which adds overhead

The Bigger Picture

Stored procedures are one of the clearest examples of what you gain by moving from Access to SQL Server. The logic that lives in your VBA code, your saved queries, and your form event handlers can be centralized in the database, where it runs faster and is easier to secure and maintain.

If you are in the early stages of evaluating a migration, stored procedures are worth understanding now — they will shape how you design your SQL Server database and how much logic you move out of the application layer.

Work With Access Evolved

If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.

Moving your Access queries to SQL Server stored procedures? Schedule a free call and get expert help with the conversion.

Schedule a Free Call →

Thinking about migrating?

Ready to move beyond Microsoft Access?

Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#SQL Server#stored procedures#migration#T-SQL#Access migration

Keep reading

More on SQL Server