Procedura oczekuje parametru, który nie został podany

Otrzymuję błąd podczas uzyskiwania dostępu do procedury składowanej w programie SQL Server

Server Error in '/' Application.
Procedure or function 'ColumnSeek' expects parameter '@template', which was not supplied. 

Dzieje się tak, gdy wywołuję procedurę składowaną z parametrem za pośrednictwem połączenia danych .net z sql (System.data.SqlClient), mimo że dostarczam parametr. Oto mój kod.

SqlConnection sqlConn = new SqlConnection(connPath);
sqlConn.Open();

//METADATA RETRIEVAL
string sqlCommString = "QCApp.dbo.ColumnSeek";
SqlCommand metaDataComm = new SqlCommand(sqlCommString, sqlConn);
metaDataComm.CommandType = CommandType.StoredProcedure;
SqlParameter sp = metaDataComm.Parameters.Add("@template",SqlDbType.VarChar,50);
sp.Value = Template;

SqlDataReader metadr = metaDataComm.ExecuteReader();

A moja procedura składowana to:

   USE [QCApp]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO

   ALTER PROCEDURE [dbo].[ColumnSeek] 
       @template varchar(50)
   AS
   EXEC('SELECT Column_Name, Data_Type 
   FROM [QCApp].[INFORMATION_SCHEMA].[COLUMNS] 
   WHERE TABLE_NAME = ' + @template);

Próbuję dowiedzieć się, co robię źle.

Edycja: Jak się okazuje, szablon był pusty, ponieważ otrzymywałem jego wartość z parametru przekazanego przez adres URL i schrzaniłem przekazywanie parametrów adresu URL (używałem @dla i zamiast &)

Chciałbym sprawdzić kod mojej aplikacji i zobaczyć, jaką wartość ustawiasz dla @template. Podejrzewam, że jest zerowa i na tym polega problem.

Oprócz innych odpowiedzi tutaj, jeśli zapomniałeś podać:

cmd.CommandType = CommandType.StoredProcedure;

Wtedy również otrzymasz ten błąd.

Ten problem jest rzeczywiście zwykle spowodowany ustawieniem wartości parametru na null, jak wspomniano powyżej w HLGEM . Pomyślałem, że opowiem o niektórych rozwiązaniach tego problemu, które uznałem za przydatne dla osób, które nie mają doświadczenia z tym problemem.

Rozwiązaniem, które wolę, jest domyślne ustawienie parametrów procedury składowanej na NULL (lub dowolną wartość, którą chcesz), o czym wspomniał sangram powyżej, ale może zostać pominięte, ponieważ odpowiedź jest bardzo szczegółowa. Coś w rodzaju:

CREATE PROCEDURE GetEmployeeDetails
    @DateOfBirth    DATETIME = NULL,
    @Surname        VARCHAR(20),
    @GenderCode     INT = NULL,
AS

Oznacza to, że jeśli w pewnych warunkach parametr zostanie ustawiony w kodzie na wartość null, platforma .NET nie ustawi parametru, a procedura składowana użyje zdefiniowanej przez siebie wartości domyślnej. Innym rozwiązaniem, jeśli naprawdę chcesz rozwiązać problem w kodzie, byłoby użycie metody rozszerzenia, która rozwiązuje problem za Ciebie, na przykład:

public static SqlParameter AddParameter<T>(this SqlParameterCollection parameters, string parameterName, T value) where T : class
{
    return value == null ? parameters.AddWithValue(parameterName, DBNull.Value) : parameters.AddWithValue(parameterName, value);
}

Matt Hamilton ma tutaj dobry post , w którym wymieniono kilka innych świetnych metod rozszerzania w tym obszarze.

Miałem problem, w którym otrzymywałem błąd, gdy podałem 0 do parametru liczby całkowitej. I stwierdził, że:

cmd.Parameters.AddWithValue("@Status", 0);

działa, ale to nie:

cmd.Parameters.Add(new SqlParameter("@Status", 0));

W moim przypadku musiałem przekazać DBNULL.Value(używając warunku if else) z kodu dla parametru procedur składowanych, które nie są zdefiniowane, nullale wartość jest null.

Podobny problem napotykam podczas wywoływania procedury składowanej

CREATE PROCEDURE UserPreference_Search
    @UserPreferencesId int,
    @SpecialOfferMails char(1),
    @NewsLetters char(1),
    @UserLoginId int,
    @Currency varchar(50)
AS
DECLARE @QueryString nvarchar(4000)

SET @QueryString = 'SELECT UserPreferencesId,SpecialOfferMails,NewsLetters,UserLoginId,Currency FROM UserPreference'
IF(@UserPreferencesId IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE UserPreferencesId = @DummyUserPreferencesId';
END

IF(@SpecialOfferMails IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE SpecialOfferMails = @DummySpecialOfferMails';
END

IF(@NewsLetters IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE NewsLetters = @DummyNewsLetters';
END

IF(@UserLoginId IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE UserLoginId = @DummyUserLoginId';
END

IF(@Currency IS NOT NULL)
BEGIN
SET @QueryString = @QueryString + ' WHERE Currency = @DummyCurrency';
END

EXECUTE SP_EXECUTESQL @QueryString
                     ,N'@DummyUserPreferencesId int, @DummySpecialOfferMails char(1), @DummyNewsLetters char(1), @DummyUserLoginId int, @DummyCurrency varchar(50)'
                     ,@DummyUserPreferencesId=@UserPreferencesId
                     ,@DummySpecialOfferMails=@SpecialOfferMails
                     ,@DummyNewsLetters=@NewsLetters
                     ,@DummyUserLoginId=@UserLoginId
                     ,@DummyCurrency=@Currency;

Które dynamicznie konstruując zapytanie wyszukiwania, wołałem powyżej jeden przez:

public DataSet Search(int? AccessRightId, int? RoleId, int? ModuleId, char? CanAdd, char? CanEdit, char? CanDelete, DateTime? CreatedDatetime, DateTime? LastAccessDatetime, char? Deleted)
    {
        dbManager.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
        DataSet ds = new DataSet();
        try
        {
            dbManager.Open();
            dbManager.CreateParameters(9);
            dbManager.AddParameters(0, "@AccessRightId", AccessRightId, ParameterDirection.Input);
            dbManager.AddParameters(1, "@RoleId", RoleId, ParameterDirection.Input);
            dbManager.AddParameters(2, "@ModuleId", ModuleId, ParameterDirection.Input);
            dbManager.AddParameters(3, "@CanAdd", CanAdd, ParameterDirection.Input);
            dbManager.AddParameters(4, "@CanEdit", CanEdit, ParameterDirection.Input);
            dbManager.AddParameters(5, "@CanDelete", CanDelete, ParameterDirection.Input);
            dbManager.AddParameters(6, "@CreatedDatetime", CreatedDatetime, ParameterDirection.Input);
            dbManager.AddParameters(7, "@LastAccessDatetime", LastAccessDatetime, ParameterDirection.Input);
            dbManager.AddParameters(8, "@Deleted", Deleted, ParameterDirection.Input);
            ds = dbManager.ExecuteDataSet(CommandType.StoredProcedure, "AccessRight_Search");
            return ds;
        }
        catch (Exception ex)
        {
        }
        finally
        {
            dbManager.Dispose();
        }
        return ds;
    }

Następnie po wielu drapaniu głowy zmodyfikowałem procedurę składowaną na:

ALTER PROCEDURE [dbo].[AccessRight_Search]
    @AccessRightId int=null,
    @RoleId int=null,
    @ModuleId int=null,
    @CanAdd char(1)=null,
    @CanEdit char(1)=null,
    @CanDelete char(1)=null,
    @CreatedDatetime datetime=null,
    @LastAccessDatetime datetime=null,
    @Deleted char(1)=null
AS
DECLARE @QueryString nvarchar(4000)
DECLARE @HasWhere bit
SET @HasWhere=0

SET @QueryString = 'SELECT a.AccessRightId, a.RoleId,a.ModuleId, a.CanAdd, a.CanEdit, a.CanDelete, a.CreatedDatetime, a.LastAccessDatetime, a.Deleted, b.RoleName, c.ModuleName FROM AccessRight a, Role b, Module c WHERE a.RoleId = b.RoleId AND a.ModuleId = c.ModuleId'

SET @HasWhere=1;

IF(@AccessRightId IS NOT NULL)
    BEGIN
        IF(@HasWhere=0) 
            BEGIN
                SET @QueryString = @QueryString + ' WHERE a.AccessRightId = @DummyAccessRightId';
                SET @HasWhere=1;
            END
        ELSE                SET @QueryString = @QueryString + ' AND a.AccessRightId = @DummyAccessRightId';
    END

IF(@RoleId IS NOT NULL)
    BEGIN
        IF(@HasWhere=0)
            BEGIN   
                SET @QueryString = @QueryString + ' WHERE a.RoleId = @DummyRoleId';
                SET @HasWhere=1;
            END
        ELSE            SET @QueryString = @QueryString + ' AND a.RoleId = @DummyRoleId';
    END

IF(@ModuleId IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
            BEGIN   
                SET @QueryString = @QueryString + ' WHERE a.ModuleId = @DummyModuleId';
                SET @HasWhere=1;
            END
    ELSE SET @QueryString = @QueryString + ' AND a.ModuleId = @DummyModuleId';
END

IF(@CanAdd IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
            BEGIN       
                SET @QueryString = @QueryString + ' WHERE a.CanAdd = @DummyCanAdd';
                SET @HasWhere=1;
            END
    ELSE SET @QueryString = @QueryString + ' AND a.CanAdd = @DummyCanAdd';
END

IF(@CanEdit IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
        BEGIN
            SET @QueryString = @QueryString + ' WHERE a.CanEdit = @DummyCanEdit';
            SET @HasWhere=1;
        END
    ELSE SET @QueryString = @QueryString + ' AND a.CanEdit = @DummyCanEdit';
END

IF(@CanDelete IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
        BEGIN
            SET @QueryString = @QueryString + ' WHERE a.CanDelete = @DummyCanDelete';
            SET @HasWhere=1;
        END
    ELSE SET @QueryString = @QueryString + ' AND a.CanDelete = @DummyCanDelete';
END

IF(@CreatedDatetime IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
    BEGIN
        SET @QueryString = @QueryString + ' WHERE a.CreatedDatetime = @DummyCreatedDatetime';
        SET @HasWhere=1;
    END
    ELSE SET @QueryString = @QueryString + ' AND a.CreatedDatetime = @DummyCreatedDatetime';
END

IF(@LastAccessDatetime IS NOT NULL)
BEGIN
    IF(@HasWhere=0) 
        BEGIN
            SET @QueryString = @QueryString + ' WHERE a.LastAccessDatetime = @DummyLastAccessDatetime';
            SET @HasWhere=1;
        END
    ELSE SET @QueryString = @QueryString + ' AND a.LastAccessDatetime = @DummyLastAccessDatetime';
END

IF(@Deleted IS NOT NULL)
BEGIN
  IF(@HasWhere=0)   
    BEGIN
        SET @QueryString = @QueryString + ' WHERE a.Deleted = @DummyDeleted';
        SET @HasWhere=1;
    END
  ELSE SET @QueryString = @QueryString + ' AND a.Deleted = @DummyDeleted';
END

PRINT @QueryString

EXECUTE SP_EXECUTESQL @QueryString
                      ,N'@DummyAccessRightId int, @DummyRoleId int, @DummyModuleId int, @DummyCanAdd char(1), @DummyCanEdit char(1), @DummyCanDelete char(1), @DummyCreatedDatetime datetime, @DummyLastAccessDatetime datetime, @DummyDeleted char(1)'
                      ,@DummyAccessRightId=@AccessRightId
                      ,@DummyRoleId=@RoleId
                      ,@DummyModuleId=@ModuleId
                      ,@DummyCanAdd=@CanAdd
                      ,@DummyCanEdit=@CanEdit
                      ,@DummyCanDelete=@CanDelete
                      ,@DummyCreatedDatetime=@CreatedDatetime
                      ,@DummyLastAccessDatetime=@LastAccessDatetime
                      ,@DummyDeleted=@Deleted;

TUTAJ Inicjalizuję parametry wejściowe przechowywanej procedury do wartości zerowej w następujący sposób

    @AccessRightId int=null,
@RoleId int=null,
@ModuleId int=null,
@CanAdd char(1)=null,
@CanEdit char(1)=null,
@CanDelete char(1)=null,
@CreatedDatetime datetime=null,
@LastAccessDatetime datetime=null,
@Deleted char(1)=null

to załatwiło sprawę dla Mnie.

Mam nadzieję, że będzie to pomocne dla kogoś, kto wpadnie w podobną pułapkę.

Jeśli szablon nie jest ustawiony (tj. == null), ten błąd również zostanie zgłoszony.

Więcej komentarzy:

Jeśli znasz wartość parametru do czasu dodania parametrów, możesz również użyć AddWithValue

EXEC nie jest wymagane. Możesz odwołać się do parametru @template bezpośrednio w SELECT.

Po pierwsze - dlaczego to EXEC? Nie powinno tak być

AS
SELECT Column_Name, ...
FROM ...
WHERE TABLE_NAME = @template

Obecny SP nie ma sensu? W szczególności wyszukałoby to kolumnę pasującą do @template, a nie wartość varchar @template. tj. jeśli @template to 'Column_Name', przeszukałoby WHERE TABLE_NAME = Column_Name, co jest bardzo rzadkie (aby mieć taką samą nazwę tabeli i kolumny).

Ponadto, jeśli nie trzeba używać dynamicznego SQL, należy użyć EXEC sp_ExecuteSQL(przy zachowaniu wartości jako parametry), aby zapobiec przed atakami wstrzyknięcia (zamiast konkatenacji wejścia). Ale w tym przypadku nie jest to konieczne.

Jeśli chodzi o rzeczywisty problem - na pierwszy rzut oka wygląda dobrze; czy na pewno nie masz innej kopii SP? To częsty błąd ...

Natknąłem się na ten błąd dzisiaj, gdy wartości null zostały przesłane do parametrów mojej procedury składowanej. Udało mi się łatwo naprawić, zmieniając procedurę składowaną, dodając domyślną wartość = null.

Miałem ten sam problem, aby go rozwiązać, po prostu dodaj dokładnie taką samą nazwę parametru do kolekcji parametrów, jak w procedurach składowanych.

Przykład

Powiedzmy, że tworzysz procedurę składowaną:

create procedure up_select_employe_by_ID 
     (@ID int) 
as
    select * 
    from employe_t 
    where employeID = @ID

Więc pamiętaj, aby nazwać swój parametr dokładnie tak, jak w procedurze składowanej

cmd.parameter.add("@ID", sqltype,size).value = @ID

Jeśli pójdziesz

cmd.parameter.add("@employeID", sqltype,size).value = @employeid 

wtedy pojawia się błąd.

Konieczne jest poinformowanie, że wywoływany jest zapisany proces:

comm.CommandType = CommandType.StoredProcedure;