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

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


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
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

        @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);


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
            .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?

5/9/2019 10:01:41 AM

Accepted Answer

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)
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.

5/9/2019 9:05:34 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow