SqlException: "Deve dichiarare la variabile scalare" nella procedura memorizzata di esecuzione

Question

SqlException: "Deve dichiarare la variabile scalare" nella procedura memorizzata di esecuzione

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

Domanda

Ho scritto una procedura memorizzata:

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;

Lo chiamo in un servizio in questo modo:

        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;

Tutti i parametri sono impostati su null in esecuzione, ma ottengo questo errore:

SqlException: deve dichiarare la variabile scalare "@UserId".

Il progetto è sviluppato con ASP.net core 2.2 se aiuta.

Che cosa sto facendo di sbagliato?

Pourya8366

Vedi di più su Stack Overflow

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

Questo è un caso del problema XY . Hai un problema con X, (come aggiungere le condizioni della query in modo dinamico) e supponiamo che Y sia la soluzione (query generali). Quando incontri problemi con Y, però, chiedi di Y invece del problema originale, X.

Rispondere a Y

In SQL Server il nome di un parametro inizia sempre con @ . Il nome del parametro definito in EF Core dovrebbe essere @ResultStat o @UserID , non solo ResultStat o UserID , ad esempio:

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

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

Evitare tutte le procedure memorizzate

Tuttavia, non è necessaria alcuna procedura memorizzata, in realtà danneggerà le prestazioni. Questo perché i piani di esecuzione della procedura memorizzata vengono creati e memorizzati nella cache per il riutilizzo la prima volta che viene chiamata una procedura memorizzata.

Il piano di esecuzione per una query che ignora un campo è molto diverso da uno che tenta di filtrare utilizzando un argomento specifico. Se la prima chiamata alla procedura memorizzata contiene un valore NULL per qualche argomento, l'ottimizzatore creerà un piano di esecuzione che non utilizza indici che coprono quel campo. Quando la procedura memorizzata viene chiamata con un valore argomento, il server utilizzerà il piano di esecuzione memorizzato nella cache che non utilizza tali indici.

Controllare il Come confondere lo Strumento per ottimizzare le query di SQL Server per una spiegazione dettagliata

Il modo semplice e veloce per aggiungere condizioni in modo dinamico

ORM come EF e lingue come LINQ rimuovono completamente la necessità di interrogazioni generali. Scrivere una query condizionale è banale:

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

Questo è tutto. EF stesso creerà una query contenente solo le condizioni desiderate, combinando più condizioni con AND .

SqlException: "Deve dichiarare la variabile scalare" nella procedura memorizzata di esecuzione

Domanda

Fastest Entity Framework Extensions

Risposta accettata

Performance

Expression Evaluator

Related