Calling Stored Procedures from ASP.NET and VB.NET

We first published this article for .NET 2.0 and Visual Studio 2005, and people still land on it every week. The advice underneath hasn’t changed much: put the SQL in the database, call it with typed parameters, read the results with the cheapest reader that fits. The code around it has changed a lot. The original samples had MsgBox calls in a web article, a WHERE clause before the FROM, and a SqlCommand that was never declared (we know, we know). This rewrite keeps the same three examples, fixes the code, and adds a section on the thing everybody asks about now: getting an AI coding agent to write the procedure for you without it quietly writing something slow.

Everything below is VB.NET, because that’s what the URL promises and what most readers arrive for. Where the C# is different enough to matter, there’s a C# block too. If you’re on ASP.NET Core, your web project is almost certainly C# (Microsoft never shipped VB templates for it), but the data-access code still compiles fine in a VB.NET class library if you’d rather not port it.

Write the procedure first

The original article spent nine numbered steps on how to create a procedure from Visual Studio’s Server Explorer. That path still exists (it’s called SQL Server Object Explorer now), but almost nobody writes procedures there. Use SQL Server Management Studio, or VS Code with the MSSQL extension, and keep the .sql file in your repository. A procedure that only exists inside the database is a procedure nobody can review or roll back.

Here’s the procedure the first example calls. Two inputs, one output, and it returns the identity of the row it inserted.

CREATE OR ALTER PROCEDURE dbo.SendMessage
    @Mobile    VARCHAR(15),
    @Message   NVARCHAR(160),
    @LastMsgID BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Messages (Mobile, MessageText, SentOn)
    VALUES (@Mobile, @Message, SYSUTCDATETIME());

    SET @LastMsgID = SCOPE_IDENTITY();
END

Two habits worth keeping from this. SET NOCOUNT ON stops SQL Server sending “1 row affected” messages back to the client, which is noise for a program and, in a loop, measurable overhead. And SCOPE_IDENTITY() rather than @@IDENTITY, because @@IDENTITY will happily return an ID from a trigger on a completely different table. That one has bitten more than one developer here.

Tip that survived from the original: a stored procedure can take at most 2,100 parameters. If you’re anywhere near that, pass a table-valued parameter or JSON instead.

Connection and command setup

Two things changed since the .NET 2.0 days. The namespace is now Microsoft.Data.SqlClient (a NuGet package) rather than System.Data.SqlClient; the API is the same, only the Imports line differs. And since version 4 of that package, connections are encrypted by default, so a local SQL Server with a self-signed certificate will refuse to connect until you add TrustServerCertificate=True to the connection string. Do that in development only. On production, install a real certificate.

Connection strings belong in configuration (appsettings.json on Core, web.config on Framework), never in code. The original article had User ID=User;Password=Password inline. Please don’t.

' ASP.NET Core
Dim connectionString = builder.Configuration.GetConnectionString("Default")

' ASP.NET Framework
Dim connectionString = ConfigurationManager.ConnectionStrings("Default").ConnectionString

Every example below follows the same shape: a Using block for the connection, a Using block for the command, open the connection as late as possible, and let the End Using close it. Connection pooling in SqlClient means the open/close is cheap. Holding a connection open across a request because “opening is expensive” is a 2005 habit that starves the pool in 2026.

The way not to do it

The old article showed this as “the first method” and then said it wasn’t recommended. We’ll be blunter: don’t do it.

' String-built EXEC. Injectable, and SQL Server can't reuse the plan.
cmd.CommandText = "EXEC dbo.SendMessage '" & mobile & "', '" & message & "'"

If message contains a single quote, this breaks. If it contains '; DROP TABLE dbo.Messages; --, it does worse. And even when the input is safe, every distinct string is a new query plan for SQL Server to compile and cache. Typed parameters fix all three problems in one line, so there’s no scenario where the string version wins.

Example 1: output parameter and return value

This calls dbo.SendMessage, reads the new ID back through the output parameter, and returns it. Note the parameter types and sizes match the procedure’s declaration exactly. That isn’t fussiness. If you pass an NVarChar parameter to a VARCHAR column, SQL Server has to convert the column side of the comparison and will scan the index instead of seeking it.

Imports System.Data
Imports Microsoft.Data.SqlClient

Public Class MessageRepository
    Private ReadOnly _connectionString As String

    Public Sub New(connectionString As String)
        _connectionString = connectionString
    End Sub

    Public Function SendMessage(mobile As String, message As String) As Long
        Using con As New SqlConnection(_connectionString)
            Using cmd As New SqlCommand("dbo.SendMessage", con)
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("@Mobile", SqlDbType.VarChar, 15).Value = mobile
                cmd.Parameters.Add("@Message", SqlDbType.NVarChar, 160).Value = message

                Dim idParam = cmd.Parameters.Add("@LastMsgID", SqlDbType.BigInt)
                idParam.Direction = ParameterDirection.Output

                con.Open()
                cmd.ExecuteNonQuery()

                Return CLng(idParam.Value)
            End Using
        End Using
    End Function
End Class

The same thing in C#, since this is the block people copy most:

using System.Data;
using Microsoft.Data.SqlClient;

public long SendMessage(string mobile, string message)
{
    using var con = new SqlConnection(_connectionString);
    using var cmd = new SqlCommand("dbo.SendMessage", con)
    {
        CommandType = CommandType.StoredProcedure
    };

    cmd.Parameters.Add("@Mobile", SqlDbType.VarChar, 15).Value = mobile;
    cmd.Parameters.Add("@Message", SqlDbType.NVarChar, 160).Value = message;

    var idParam = cmd.Parameters.Add("@LastMsgID", SqlDbType.BigInt);
    idParam.Direction = ParameterDirection.Output;

    con.Open();
    cmd.ExecuteNonQuery();

    return (long)idParam.Value;
}

Output parameters are populated after ExecuteNonQuery completes. If you use ExecuteReader instead, they’re not available until the reader is closed, which catches people out when they read the ID before the End Using.

A procedure can also send back an integer through RETURN, separate from any output parameters. That’s the usual place for a status code. To read it, add a parameter with direction ReturnValue; the name doesn’t matter.

Dim rv = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
rv.Direction = ParameterDirection.ReturnValue

con.Open()
cmd.ExecuteNonQuery()

Dim status = CInt(rv.Value)   ' whatever the procedure put in RETURN

And if the procedure ends with a single-row, single-column SELECT instead of an output parameter, ExecuteScalar reads that first cell directly. Pick one convention per codebase. Mixing output parameters, return values, and scalar selects for the same job is how a team ends up with three ways to get an ID and none of them documented.

Example 2: result set into a list or DataTable

The procedure:

CREATE OR ALTER PROCEDURE dbo.GetAuthors
    @AuthorName NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Author_ID, Author_Name, Author_Location
    FROM dbo.Authors
    WHERE Author_Name LIKE @AuthorName
    ORDER BY Author_Name;
END

The original filled a DataTable by hand with an object array. If you want a DataTable, table.Load(reader) does it in one line. But most code written today wants a typed list, so that’s the main example. Read the column ordinals once, outside the loop, and check for DBNull on nullable columns before calling the typed getter. GetString on a NULL throws.

Public Class Author
    Public Property Id As Integer
    Public Property Name As String
    Public Property Location As String
End Class

Public Function GetAuthors(namePattern As String) As List(Of Author)
    Dim authors As New List(Of Author)

    Using con As New SqlConnection(_connectionString)
        Using cmd As New SqlCommand("dbo.GetAuthors", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar, 100).Value = namePattern

            con.Open()
            Using reader = cmd.ExecuteReader()
                Dim idOrd = reader.GetOrdinal("Author_ID")
                Dim nameOrd = reader.GetOrdinal("Author_Name")
                Dim locOrd = reader.GetOrdinal("Author_Location")

                While reader.Read()
                    authors.Add(New Author With {
                        .Id = reader.GetInt32(idOrd),
                        .Name = reader.GetString(nameOrd),
                        .Location = If(reader.IsDBNull(locOrd), Nothing, reader.GetString(locOrd))
                    })
                End While
            End Using
        End Using
    End Using

    Return authors
End Function

If a DataTable is what the calling code expects (a GridView on Web Forms, say), replace the loop with this:

Using reader = cmd.ExecuteReader()
    Dim table As New DataTable()
    table.Load(reader)
    Return table
End Using

Pass the wildcard from the caller (GetAuthors("Y%")), not from inside the procedure. A procedure that appends % itself can’t be used for an exact match later without a second procedure.

Example 3: XML and JSON results

The original’s third example returned XML with FOR XML AUTO. That still works, but in the rewrite we’ve switched to FOR XML PATH with a ROOT, because AUTO returns a fragment with no root element and XDocument.Load won’t accept it. PATH also gives you control over element names, which AUTO doesn’t.

CREATE OR ALTER PROCEDURE dbo.GetAuthorsXML
    @AuthorName NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Author_ID   AS [@id],
           Author_Name AS [Name],
           Author_Location AS [Location]
    FROM dbo.Authors
    WHERE Author_Name LIKE @AuthorName
    FOR XML PATH('Author'), ROOT('Authors');
END

ExecuteXmlReader hands you an XmlReader positioned on the result, and XDocument.Load takes it from there. The original called ExecuteReader and then ExecuteXmlReader on the same command, which is one call too many.

Imports System.Xml.Linq

Public Function GetAuthorsXml(namePattern As String) As XDocument
    Using con As New SqlConnection(_connectionString)
        Using cmd As New SqlCommand("dbo.GetAuthorsXML", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar, 100).Value = namePattern

            con.Open()
            Using reader = cmd.ExecuteXmlReader()
                Return XDocument.Load(reader)
            End Using
        End Using
    End Using
End Function

If you’re starting a new project today, you probably want JSON, not XML. SQL Server 2016 and later support FOR JSON PATH, and the calling side is a plain reader. One catch: SQL Server splits long JSON output across multiple rows of about 2,000 characters each, so you have to concatenate. There’s no ExecuteJsonReader.

' Procedure ends with:  ... WHERE Author_Name LIKE @AuthorName FOR JSON PATH;

Public Function GetAuthorsJson(namePattern As String) As String
    Dim sb As New StringBuilder()

    Using con As New SqlConnection(_connectionString)
        Using cmd As New SqlCommand("dbo.GetAuthorsJSON", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar, 100).Value = namePattern

            con.Open()
            Using reader = cmd.ExecuteReader()
                While reader.Read()
                    sb.Append(reader.GetString(0))
                End While
            End Using
        End Using
    End Using

    Return sb.ToString()
End Function

Async and ASP.NET Core

Everything above is synchronous, which is fine on Web Forms and in a console tool. On ASP.NET Core, a synchronous database call holds a thread-pool thread for the whole round trip, and under load that’s the difference between serving 500 concurrent requests and queueing them. Every method used above has an Async twin. The shape doesn’t change; you add Await in three places.

Public Async Function GetAuthorsAsync(namePattern As String) As Task(Of List(Of Author))
    Dim authors As New List(Of Author)

    Using con As New SqlConnection(_connectionString)
        Using cmd As New SqlCommand("dbo.GetAuthors", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar, 100).Value = namePattern

            Await con.OpenAsync()
            Using reader = Await cmd.ExecuteReaderAsync()
                Dim idOrd = reader.GetOrdinal("Author_ID")
                Dim nameOrd = reader.GetOrdinal("Author_Name")
                Dim locOrd = reader.GetOrdinal("Author_Location")

                While Await reader.ReadAsync()
                    authors.Add(New Author With {
                        .Id = reader.GetInt32(idOrd),
                        .Name = reader.GetString(nameOrd),
                        .Location = If(reader.IsDBNull(locOrd), Nothing, reader.GetString(locOrd))
                    })
                End While
            End Using
        End Using
    End Using

    Return authors
End Function

If your project already uses Dapper or Entity Framework Core, both call procedures with less ceremony (con.QueryAsync(Of Author)("dbo.GetAuthors", New With {.AuthorName = "Y%"}, commandType:=CommandType.StoredProcedure) in Dapper’s case). This article stays on raw SqlClient because that’s what the other two run on underneath, and when either of them misbehaves, this is the layer you end up reading.

Which Execute method to use

Writing stored procedures with an AI coding agent

A good share of the .NET work that comes through us now involves Claude Code, GitHub Copilot, or Cursor somewhere in the loop, and stored procedures are one of the better jobs to hand them. The task is small, the contract is explicit (these inputs, this output, this table), and the result is testable with a five-line script. The agent gets the T-SQL right most of the time. What it can’t do is see your data, and that’s where the problems are.

The prompt matters more than the tool. Give the agent the actual CREATE TABLE script (right-click, Script Table As, paste it), name the SQL Server version, state the contract, and ask for the test in the same request. A prompt that works for us looks like this:

Here is the DDL for dbo.Messages: (paste the CREATE TABLE script)
Target: SQL Server 2019.

Write dbo.SendMessage with inputs @Mobile VARCHAR(15), @Message NVARCHAR(160)
and output @LastMsgID BIGINT. Requirements: SET NOCOUNT ON, no dynamic SQL,
TRY/CATCH that rethrows with THROW, parameter types must match the column types
exactly. Do not alter the table.

Then write a T-SQL test script that calls it twice and checks the second
@LastMsgID is greater than the first. Save both as separate .sql files.

Once the procedure exists, ask the agent to generate the VB.NET or C# calling method from it in the same session. Parameter-name typos and type mismatches between the procedure and the client code are the most common bug in this whole area, and having one tool write both sides removes most of them.

Then review. The agent doesn’t know your row counts, your indexes, or which parameter values are common and which are rare, so the review is mostly about performance and safety, and it’s short:

• Parameter types and lengths match the column types exactly (an NVARCHAR parameter against a VARCHAR column scans the index).
• SET NOCOUNT ON is the first statement.
• No dynamic SQL. If it’s unavoidable, it’s sp_executesql with parameters, never concatenation.
• TRY/CATCH rethrows with THROW. Agents like to RAISERROR a friendly message and swallow the original error, which loses the line number.
• Any transaction is committed or rolled back on every path, including the CATCH.
• Column lists are explicit. SELECT * in a procedure breaks the calling code the day someone adds a column.
• You’ve looked at the actual execution plan with production-sized data. The agent tested against an empty table, and everything seeks on an empty table.
• The .sql file is committed alongside the .NET change, so the next agent session (or the next developer) can see what changed and why.

That last one is the one teams skip. An agent that edits a procedure directly in the database, with no file in the repo, has made a change nobody can diff. Keep procedures in a database project or a migrations folder, point the agent at that folder, and the whole workflow becomes reviewable in a pull request like any other code.

Related reading

• What is a stored procedure? A primer
• Stored procedure tips
• Stored procedures for classic ASP and VB programmers