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