How to store more than 8000 characters and assign large concatenated string in SQL server 2008?

Question

0.00/5 (No votes)

See more:

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)

Please check my stored Procedure:
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


Please check my code behind:
In code i am getting all the id's may be more than 2000-3000, but it is updating only 1000 rows..how can i increase nvarchar(max) size , to take all id's.


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

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 then 8000 character, it's not taking more than 8000 character in stored procedure

What I have tried:

Please check my stored Procedure:
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


Please check my code behind:
In code i am getting all the id's may be more than 2000-3000, but it is updating only 1000 rows..how can i increase nvarchar(max) size , to take all id's.


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

Posted 27-Nov-17 0:30am

Member 13011316

Updated 1-Dec-17 5:47am

Add a Solution

Comments

Mehdi Gholam 27-Nov-17 6:39am

Batch your id's

Member 13011316 27-Nov-17 6:43am

Can you please help with more detail. how can i batch all id's

F-ES Sitecore 27-Nov-17 6:50am

Concatenated IDs isn't a good solution, it's going to give all sorts of problems. Instead use parent\child tables (also called one-to-many) and store each ID in its own row in the child table. That will let you do things like joining on the IDs and so on.

Jörgen Andersson 28-Nov-17 4:53am

You should seriously rethink your solution, it's not normalized and this is just the first problem you'll encounter.
Do follow F-ES Sitecores suggestion.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

James Jensen · Answer 1 · 2017-12-01T05:47:00

Perhaps the best way to do this in SQL Server 2008 is to use a user-defined table variable to hold the collection of id values, instead of concatenating them. When you do this, you can send an IEnumerable containing the id numbers to SQL Server and you are generally free to capture and send as many as you need. For example:

User Defined Table Variable:
------------------------------

CREATE TYPE [dbo].[MyIDType] AS TABLE(
    id int,
    status varchar(10)
)

Stored Procedure Accepting UDT (just one possible method):
-------------------------------

CREATE PROCEDURE [dbo].[VT_ACLReportChangeStatus] (
    @idlist MyIDType READONLY
)
AS
BEGIN
    ;
    MERGE dbo.VT_ACLReportChangeStatus dst
    USING @idlist src
    ON src.id = dst.id
    WHEN MATCHED THEN
        UPDATE SET
            dst.status = src.status
    ;
END
GO

Then in your code, you could create a list, fill it with the id values and statuses, and pass it as a parameter. For example (not tested, just off the top of my head) the associated "row" record could be:

public class MyStatusRecord {
    public int id { get; set; }
    public string status { get; set; }
}

The rows need to be in a collection that defines the SqlMetaData. This provides the proper interface and typing to send an IEnumerable to a stored proc as a UDT:

public class MyStatusCollection : List<MyStatusRecord>, IEnumerable<SqlDataRecord> {
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator() {
        var data = new SqlDataRecord(
            new SqlMetaData("id", SqlDbType.Int),
            new SqlMetaData("status", SqlDbType.VarChar, 10)
        );
        foreach (var row in this) {
            data.SetInt32(0, row.id);
            data.SetString(1, row.status);

            yield return data;
        }
    }
}

You can then generate a list of values, something like:

MyStatusCollection mylist = new MyStatusCollection {
    new MyStatusRecord { id = 1, status = "changed" },
    new MyStatusRecord { id = 2, status = "deleted" },
// -- and so forth --
}

Then you can push that to the database like this:

private void UpsertData()
{
    using (var conn = Connections.GetConnection()) // -- or however you get a connection
    {
        using (var cmd = new SqlCommand("VT_ACLReportChangeStatus", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@idlist", SqlDbType.Structured)
            {
                TypeName = "dbo.MyIDType",
                Value = mylist
            });
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

All the relevant information can be gleaned from the MSDN sql server pages online, so don't just copy what I created here...look it up and figure it out.

Good luck! :)