Nishanth J

Nishanth J

  • 1.8k
  • 95
  • 210

List with Parameter’s object name gets Prefixed with @

Feb 9 2020 8:14 AM
Before passing to the Stored Procedure, all the parameters will be prefixed with @. i.e, outside the region of my foreach loop and from the second iteration all the parameters name will prefixed to @ and getting an exception message from the second iteration, when a call is made to the stored procedure
{"Procedure or function UspUpdateProctorSignalByScheduleUserID has too many arguments specified."}
Here is the partial code of the snippets shown below:
  1. [WebMethod]    
  2. public static List<OnlineProctor> UpdateAllProctorStatus(ProctoringScheduledUserInfoList scheduledUsersList)    
  3. {    
  4.     #region Outside of foreach loop parameter's name will be prefixed to @ from the second iteration of the loop    
  5.         List<object> parameters = new List<object>();    
  6.     
  7.         parameters.Add(SqlHelper.BuildSqlParameter("Status", SqlDbType.VarChar, 10, "status"null, ParameterDirection.Output));    
  8.     
  9.         if (proctorStatus == "PAUSE")    
  10.             parameters.Add(SqlHelper.BuildSqlParameter("ScheduleExtensionTime", SqlDbType.Int, sizeof(Int16), "ScheduleExtensionTime", scheduledUsersList.PauseTime));    
  11.     
  12.         parameters.Add(SqlHelper.BuildSqlParameter("ProctorSignal", SqlDbType.TinyInt, sizeof(Int16), "ProctorSignal", scheduledUsersList.ProctorSignal));    
  13.   
  14.     #endregion    
  15.     
  16.     foreach (Int64 scheduleUserID in scheduledUsersList.ScheduleUserID)    
  17.     {    
  18.         parameters.Add(SqlHelper.BuildSqlParameter("ScheduleDetailUserID", SqlDbType.BigInt, sizeof(Int64), "ScheduleDetailUserID", scheduleUserID));    
  19.     
  20.         proctoringUserListDT = SqlHelper.ExecuteDataTable(CommandType.StoredProcedure, "UspUpdateProctorSignalByScheduleUserID", parameters.ToArray(), false);    
  21.     
  22.         if (proctorStatus == "PAUSE")    
  23.             parameters.RemoveAt(3);    
  24.         else    
  25.             parameters.RemoveAt(2);    
  26.     }    
  27.     
  28. }  
Actual Result:
a. For the I iteration
parameters | Count = 4
[0] {Status}
Value null
[1] {ScheduleExtensionTime}
Value 0
[2] {ProctorSignal}
Value 1
[3] {ScheduleDetailUserID}
Value 943417
declare @p1 varchar(10)
set @p1='S001'
exec UspUpdateProctorSignalByScheduleUserID @Status=@p1 output,@ScheduleExtensionTime=0,@ProctorSignal=1,@ScheduleDetailUserID=943417
select @p1
b. For the II iteration
parameters | Count = 4
[0] {@Status}
Value "S001"
[1] {@ScheduleExtensionTime}
Value 0
[2] {@ProctorSignal}
Value 1
[3] {ScheduleDetailUserID}
Value 943419
declare @p1 varchar(10)
set @p1=NULL
exec UspUpdateProctorSignalByScheduleUserID @@Status=@p1 output,@@ScheduleExtensionTime=0,@@ProctorSignal=1,@ScheduleDetailUserID=943419
select @p1
 
Expected Result:
b. For the II iteration
parameters | Count = 4
[0] {Status}
Value null
[1] {ScheduleExtensionTime}
Value 0
[2] {ProctorSignal}
Value 1
[3] {ScheduleDetailUserID}
Value 943419
declare @p1 varchar(10)
set @p1='S001'
exec UspUpdateProctorSignalByScheduleUserID @Status=@p1 output,@ScheduleExtensionTime=0,@ProctorSignal=1,@ScheduleDetailUserID=943419
select @p1
 
Here the property gets changed from the second iteration onward
 
> ParameterName = "@Status"
> ParameterNameFixed = "@Status"
> _parameterName = "@Status"
 
The same piece of snippets when declared and initialized within foreach loop works perfectly.
But inside the foreach loop, the condition if (proctorStatus == "PAUSE") checks whether to remove parameters.RemoveAt(3); the list at particular index 3 or not.
Does the same condition can be written making use of LINQ or Lambda operator i.e, by removing the object for particular ScheduleDetailUserID within each iteration of the loop?