I am trying to concatenate the large number of id'd and to update the status of all id's. For example: aclid in (4604019,4604018,4604017,4604016,4604015,4604014,4604013,4604012,4604011,4604010,4604009,4604008,4604007,4604006,4604005,4604004,4604003,4604002,4604001,4604000,4603999,4603998,4603997,4603996,4603995,4603994,4603993,4603992,4603991,4603990,4603989,4603988)
AclId Column type is bigint is identity.
Please can you help me in concatenating large string and to update all rows whose aclid is present.
According to my requirement, i have to update the status of selected id's in the same table. so i concatenated all selected id's and then trying to update the rows in one go. But problem is coming if the more than 8000 character, it's not taking more than 8000 character in stored procedure
ACLId = ACLId.ToString().Trim(',');
using (SqlConnection con = new SqlConnection(cs))
{
cmd = new SqlCommand("VT_ACLReportChangeStatus", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 3600;
cmd.Parameters.Add(new SqlParameter("@ACLId", SqlDbType.NVarChar,-1));
cmd.Parameters.Add(new SqlParameter("@ChangeStatus", SqlDbType.NVarChar, 50));
cmd.Parameters["@ACLId"].Value = ACLId;
cmd.Parameters["@ChangeStatus"].Value = ddlChangeStatus.SelectedItem.Text.ToString();
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
ALTER PROCEDURE [dbo].[VT_ACLReportChangeStatus]
(
@ChangeStatus nvarchar(50)=null,
@ACLId nvarchar(max)
)
AS
/* Exec VT_ACLReportChangeStatus 'Complete','4599473,4599472,4599471,4599469,4599468' */
BEGIN
UPDATE VT_ACLReport SET Status = @ChangeStatus WHERE ACLId in (Select * from SplitDelimiterString(@ACLId,','))
End
