SqlException: "Must declare the scalar variable" in execution stored procedure

Question

SqlException: "Must declare the scalar variable" in execution stored procedure

.net-core entity-framework-core sql-server stored-procedures

Question

I wrote a stored procedure:

CREATE PROCEDURE [dbo].[GetAudioBookStats]
    @UserId NVARCHAR(450) = NULL
    ,@ArtistId BIGINT = 0
    ,@PublisherId BIGINT = 0
    ,@AudioBookId BIGINT = 0
    ,@SubscriptionType INT = 0
    ,@ResultStat BIGINT OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

SELECT  DISTINCT
        @ResultStat = COUNT (*)
FROM    dbo.AudioBookDownload AS d
        LEFT JOIN dbo.AudioBooks AS b ON d.AudioBookId = b.Id
WHERE   (   @ArtistId IS NULL
            OR  b.WriterArtistId = @ArtistId
            OR  b.TranslatorArtistId = @ArtistId
            OR  b.NarratorArtistId = @ArtistId)
        AND (@PublisherId IS NULL
                OR b.PublisherId = @PublisherId)
        AND (@AudioBookId IS NULL
                OR b.Id = @AudioBookId)
        AND (@SubscriptionType IS NULL
                OR d.SubscriptionType = @SubscriptionType)
        AND (@UserId IS NULL
                OR d.UserId = @UserId);

END;

I call it in a service in this way:

        var outDownloadCount = new SqlParameter("ResultStat", SqlDbType.BigInt)
        {
            Direction = ParameterDirection.Output
        };

        const string sql = "EXECUTE dbo.[GetAudioBookStats] @UserId, @ArtistId, @PublisherId, @AudioBookId, @SubscriptionType, @ResultStat OUTPUT";

        await context
            .Database
            .ExecuteSqlCommandAsync(sql, userId, artistId, publisherId, audioBookId, subscriptionType, outDownloadCount);

        var result = (int)outDownloadCount.Value;

All parameters are set to null in execution, but I get this error:

SqlException: Must declare the scalar variable "@UserId".

The project is developed with ASP.net core 2.2 if it helps.

What am I doing wrong?

Pourya8366

View more on Stack Overflow

Panagiotis Kanavos · Accepted Answer · 5/9/2019 9:05:34 AM

This is a case of the XY Problem. You have a problem with X, (how to add query conditions dynamically) and assume Y is the solution (catch-all queries). When you run into problems with Y though, you ask about Y instead of the original problem, X.

Answering Y

In SQL Server a parameter name always starts with @. The parameter name you define in EF Core should be @ResultStat or @UserID, not just ResultStat or UserID, eg:

var userId = new SqlParameter("@UserID", SqlDbType.NVarChar,450);

var outDownloadCount = new SqlParameter("@ResultStat", SqlDbType.BigInt)
{
        Direction = ParameterDirection.Output
};

Avoid catch-all stored procedures

You don't need that catch-all stored procedure at all though, in fact it will harm performance. That's because stored procedure execution plans are created and cached for reuse the first time a stored procedure is called.

The execution plan for a query that ignores a field is very different from one that tries to filter using a specific argument. If the first call to the stored procedure contains a NULL for some argument, the optimizer will create an execution plan that doesn't use indexes covering that field. When the stored procedure gets called with an argument value, the server will use the cached execution plan that doesn't use those indexes.

Check the aptly named How to Confuse the SQL Server Query Optimizer for a detailed explanation

The easy and fast way to add conditions dynamically

ORMs like EF and languages like LINQ completely remove the need for catch-all queries. Writing a conditional query is trivial:

var query=context.AudioBookDownloads;
if (someUserId!=null)
{
    query=query.Where(dl=>dl.UserId==someUserId);
}
if (someArtistId>0)
{
    query=query.Where(dl=> dl.Book.ArtistId == someArtistId 
                        || dl.Bookbook.WriterArtistId == someArtistId );
}
....

var count=query.CountAsync();

That's it. EF itself will create a query containing only the conditions you want, combining multiple conditions with AND.

SqlException: "Must declare the scalar variable" in execution stored procedure

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related