Die Eingabeparameter können nicht in die gespeicherte Prozedur übergeben werden

Ich habe memory-Prozedur wie dies immer Werte aus der database auf der Grundlage der Suchkriterien, die ich übergeben diese sind als Eingabeparameter zu diesem Verfahren, aber ich @StatusSelection Fehler bei @StatusSelection

 CREATE PROCEDURE [dbo].[tp_SelectTransactionHistorySearch] ( @OffSetRowNo INT, @FetchRowNo INT, @StatusSelection NVARCHAR(MAX), @isReviewed Bit, @ProjectCaseNumber VARCHAR(MAX), @CostPageNumber VARCHAR(MAX), @TransactionTypeChange VARCHAR(MAX), @DescriptionChange VARCHAR(MAX), @TrasactionCreateOnBeginDate DATETIME, @TransactionCreatedOnEndDate DATETIME, @TransactionUpdatedOnBeginDate DATETIME, @TransactionUpdateOnEndDate DATETIME, @ItemID VARCHAR(MAX) ) AS Select TH.TransactionID, TH.IsReviewed, TH.ItemID, TH.CostPageNumber, TH.Comments, TH.CreatedBy, TH.CreatedDateTime, TH.UpdatedBy, TH.UpdatedDateTime, TH.TransactionDescription, TH.TransactionTypeID, IAccrualByItem.PROJCASE, IAccrualByItem.USTSTAT as TransactionStatusID, TStatType.Name AS TransactionStatusTypeName, TStatType.Description AS TransactionStatusTypeDescription, TType.Name AS TransactionTypeName, TType.Description AS TransactionTypeDescription, COUNT(*) OVER () as TotalCount FROM TransactionHistory AS TH INNER JOIN TRVMVSDDVW001.INTERFACE_Files.dbo.DBATUSTA AS IAccrualByItem ON TH.TransactionID = CAST(IAccrualByItem.USTTRNNBR AS int) LEFT JOIN dbo.TransctionStatusType AS TStatType ON TStatType.TransactionStatusTypeID = IAccrualByItem.USTSTAT LEFT JOIN dbo.TransactionType AS TType ON TType.TransactionTypeID = CAST(TH.TransactionTypeID AS int) WHERE TStatType.Name = @StatusSelection AND TH.IsReviewed= @isReviewed AND IAccrualByItem.PROJCASE=@ProjectCaseNumber AND TH.CostPageNumber=@CostPageNumber AND TH.TransactionDescription=@TransactionTypeChange AND TType.Description=@DescriptionChange AND (TH.CreatedDateTime >= CAST(@TrasactionCreateOnBeginDate AS DATE)) AND (TH.CreatedDateTime < CAST(@TransactionCreatedOnEndDate AS DATE)) AND (TH.UpdatedDateTime >= CAST( @TransactionUpdatedOnBeginDate AS DATE)) AND (TH.UpdatedDateTime < CAST(@TransactionUpdateOnEndDate AS DATE)) @TH.StatusID= 1 GROUP BY TH.TransactionID,TH.IsReviewed,TH.ItemID,TH.CostPageNumber,TH.Comments,TH.CreatedBy,TH.CreatedDateTime, TH.UpdatedBy,TH.UpdatedDateTime, TH.TransactionDescription, TH.TransactionTypeID,IAccrualByItem.PROJCASE,TransactionStatusID, TStatType.Name,TStatType.Description,TType.Name,TType.Description ORDER BY TH.TransactionID,TH.ItemID,TH.CostPageNumber OFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWS FETCH NEXT @FetchRowNo ROWS ONLY 

Aber ich bekomme so Fehler

  `Msg 137, Level 15, State 2, Line 27 Must declare the scalar variable "@StatusSelection"`. 

Ich verwende SQL server 2003 Version

würde irgendjemand pls bei dieser lösung helfen und irgendwelche ideen auf diesem .. vielen dank …..

AKTUALISIEREN:

  DECLARE @return_value int EXEC @return_value = [dbo].[tp_SelectTransactionHistorySearch] @OffSetRowNo = 1, @FetchRowNo = 1, @StatusSelection = N's', @isReviewed = NULL, @ProjectCaseNumber = NULL, @CostPageNumber = NULL, @TransactionTypeChange = NULL, @DescriptionChange = NULL, @TrasactionCreateOnBeginDate = '10-03-2013', @TransactionCreatedOnEndDate = '20-03-2013', @TransactionUpdatedOnBeginDate = '20-05-2013', @TransactionUpdateOnEndDate = '04-06-2013', @ItemID = NULL SELECT 'Return Value' = @return_value GO 

Erhalten Sie Fehler wie dieser Msg 8114, Level 16, State 5, Procedure tp_SelectTransactionHistorySearch, Line 0 Error converting data type varchar to datetime .

Probier diese –

 CREATE PROCEDURE [dbo].[tp_SelectTransactionHistorySearch] ( @OffSetRowNo INT, @FetchRowNo INT, @StatusSelection NVARCHAR(MAX), @isReviewed BIT, @ProjectCaseNumber VARCHAR(MAX), @CostPageNumber VARCHAR(MAX), @TransactionTypeChange VARCHAR(MAX), @DescriptionChange VARCHAR(MAX), @TrasactionCreateOnBeginDate DATE, @TransactionCreatedOnEndDate DATE, @TransactionUpdatedOnBeginDate DATE, @TransactionUpdateOnEndDate DATE, @ItemID VARCHAR(MAX) ) AS SELECT TH.TransactionID , TH.IsReviewed , TH.ItemID , TH.CostPageNumber , TH.Comments , TH.CreatedBy , TH.CreatedDateTime , TH.UpdatedBy , TH.UpdatedDateTime , TH.TransactionDescription , TH.TransactionTypeID , IAccrualByItem.PROJCASE , IAccrualByItem.USTSTAT AS TransactionStatusID , TStatType.Name AS TransactionStatusTypeName , TStatType.[description] AS TransactionStatusTypeDescription , TType.Name AS TransactionTypeName , TType.[description] AS TransactionTypeDescription , COUNT(*) OVER () AS TotalCount FROM TransactionHistory AS TH JOIN TRVMVSDDVW001.INTERFACE_Files.dbo.DBATUSTA AS IAccrualByItem ON TH.TransactionID = CAST(IAccrualByItem.USTTRNNBR AS INT) LEFT JOIN dbo.TransctionStatusType AS TStatType ON TStatType.TransactionStatusTypeID= IAccrualByItem.USTSTAT LEFT JOIN dbo.TransactionType AS TType ON TType.TransactionTypeID = CAST(TH.TransactionTypeID AS INT) WHERE TStatType.Name = @StatusSelection AND TH.IsReviewed = @isReviewed AND IAccrualByItem.PROJCASE = @ProjectCaseNumber AND TH.CostPageNumber = @CostPageNumber AND TH.TransactionDescription = @TransactionTypeChange AND TType.[description] = @DescriptionChange AND TH.CreatedDateTime BETWEEN @TrasactionCreateOnBeginDate AND @TransactionCreatedOnEndDate AND TH.UpdatedDateTime BETWEEN @TransactionUpdatedOnBeginDate AND @TransactionUpdateOnEndDate AND TH.StatusID = 1 GROUP BY TH.TransactionID , TH.IsReviewed , TH.ItemID , TH.CostPageNumber , TH.Comments , TH.CreatedBy , TH.CreatedDateTime , TH.UpdatedBy , TH.UpdatedDateTime , TH.TransactionDescription , TH.TransactionTypeID , IAccrualByItem.PROJCASE , TransactionStatusID , TStatType.Name , TStatType.[description] , TType.Name , TType.[description] ORDER BY TH.TransactionID , TH.ItemID , TH.CostPageNumber OFFSET (@OffSetRowNo - 1) * @FetchRowNo ROWS FETCH NEXT @FetchRowNo ROWS ONLY 

Update # 1:

Setzen Sie diese Option, bevor Sie Ihre Abfrage ausführen –

 SET DATEFORMAT dmy 

oder, bevorzugter, verwenden ISO Format yyyymmdd

 EXEC @return_value = [dbo].[tp_SelectTransactionHistorySearch] ... @TrasactionCreateOnBeginDate = '20130310', @TransactionCreatedOnEndDate = '20132003', @TransactionUpdatedOnBeginDate = '20130520', @TransactionUpdateOnEndDate = '20130604', @ItemID = NULL 

Update # 2:

 DECLARE @temp TABLE ( string VARCHAR(10) ) SET DATEFORMAT dmy INSERT INTO @temp (string) VALUES ('10-03-2013'), ('20-03-2013'), ('20-05-2013'), ('04-06-2013') SELECT CAST(string AS DATE) FROM @temp 

Die Linie

 @TH.StatusID= 1 

bedeutet, dass die CREATE fehlschlägt

So kannst du nicht den Fehler bekommen, den du mit dem Code gezeigt hast, den du uns gegeben hast

Damit funktioniert CREATE

  AND TH.StatusID= 1 

Hast du die Werte hinter dem Code hinterlassen?

  SqlCommand com = new SqlCommand("SpName",conectionStringObj); com.CommandType = CommandType.StoredProcedure; **com.Parameters.AddWithValue("@StatusSelection", "Any Value");** . . . . 

Bitte prüfen Sie das .