Store multiple values from SELECT statement and then insert into different table

Question

I want to use a SELECT statement into a table based on multiple values from ListBox and get multiple IDs and then run an INSERT statement and store the multiple IDs into a different table from INSERT statement.

My code below is not working as I am getting "NULL" in single row instead of multiple IDs in multiple rows.

I am using a stored procedure for all the SQL statements.

Please see my code below:

Code-behind of my ASPX web page:

string listboxvalue = "";

foreach (ListItem item in listbox.Items)
{
    if (item.Selected)
    {
        listboxvalue += item.Text + ',';
    }
}

listboxvalue = listboxvalue.Substring(0, listboxvalue.Length - 1);
cmd.Parameters.AddWithValue("spselectvalue", listboxvalue);

Stored procedure:

@spselectvalue nvarchar(MAX),

// Select multiple Ids based on multiple items from list box

DECLARE @Dis TABLE (DisID int)

INSERT INTO @Dis 
    SELECT DId 
    FROM [table name] 
    WHERE [COLUMN] IN ('+@spselectvalue +')

EXEC sp_executesql @Dis

// Insert multiple Ids (from above select statement) into different table
INSERT INTO [dbo].[DifferentTable] ([SelectedIds])
VALUES
(
    (SELECT DisID from @Dis)
)

does @Dis contain the data you expect? – SteinTheRuler Sep 08 '17 at 23:59 — SteinTheRuler, Sep 08 '17 at 23:59

John Wu · Answer 1 · 2017-09-09T01:04:58.743

Seems like your problem is that you don't know how to get a list of ID's into a stored procedure for processing. Hopefully this example will help. If you do it this way, you won't have to mess with comma-delimited strings or dynamic SQL.

First, define a SQL type that can contain your list of IDs:

CREATE TYPE dbo.MyList
AS TABLE
(
    ID VarChar(50)
);

Then, write a stored procedure that accepts this type as its input parameter:

CREATE PROCEDURE dbo.InsertAList
    @List AS dbo.MyList READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[DifferentTable] 
    SELECT ID FROM @List
END

Now to bind your data to the stored procedure from the c# end. To bind it, you have to store the data in a DataTable first, which is relatively easy:

var table = new DataTable();
table.Columns.Add("ID", typeof(string));
foreach (ListItem item in listBox.Items.Where(i => i.Selected))
{
    table.Rows.Add(item.Text);
}

Then submit the table to the stored procedure like so:

var cmd = new SqlCommand()
    {
        CommandType = CommandType.StoredProcedure,
        CommandText = "InsertAList",
        Connection = myConnection
    };
cmd.Parameters.Add(new SqlParameter("@List", SqlDbType.Structured)
    {
        TypeName = "dbo.MyList",
        Value = table
    });
cmd.ExecuteNonQuery();

score 0 · Answer 2 · answered Sep 09 '17 at 00:23

That doesn't seem like a valid (or logical) SQL. The exec sp_executesql @Dis shouldn't be there or @Dis shouldn't be a table.

When using sp_executesql the parameter should be a string containing the statement to execute (documentation).

Detailed answer can be found here.

Also: the INSERT INTO @Dis ... line won't work as you expect when written that way, for the reasons mentioned in the linked question (and Erland Sommarskog blog mentioned there).

score 0 · Answer 3 · answered Sep 09 '17 at 13:42

0

Selected values considered as numeric.

DECLARE @Sqltext nvarchar(max) = 
'INSERT INTO [dbo].[DifferentTable] ([SelectedIds])
SELECT DId 
FROM [table name] 
WHERE [COLUMN] IN (' + @spselectvalue + ')'

EXEC sp_executesql @Sqltext

answered Sep 09 '17 at 13:42

Serkan Arslan

13,158
4
29
44

Store multiple values from SELECT statement and then insert into different table

3 Answers3

Linked