SQL Server - spliting string comma sperated value

Asked By shekhar kumar on 29-Oct-09 09:43 AM
I have a table channel
Fields Channel_Key int
          Channel_Order int ...

I want to write a proc in which I pass a string of  ID comma separated. Further I want to delete all record whose Id is there in the parameter. 
This is easy using in(values).
But I have to update other records on basis of each delete statement to update the Channel_Order. How Can I do it.

Thanks
Jonathan VH replied to shekhar kumar on 29-Oct-09 10:34 AM

"This is easy using in(values)."

That's only "easy" if you use dynamic SQL, which should be avoided. The better way is to create and populate a temporary table in your front-end code, and then join to that table in the SQL statement(s) in your stored procedure.

I don't understand your question, but I'll guess that you want something like this:

BEGIN TRAN;

DELETE c
FROM dbo.Channel c JOIN #Values t ON c.Channel_Key = t.Channel_Key;

WITH cte AS
(SELECT Channel_Key, ROW_NUMBER() OVER (ORDER BY Channel_Order) AS NewOrder
 FROM dbo.Channel)
UPDATE cte SET Channel_Order = NewOrder;

COMMIT TRAN;

If this is what you want, then you don't actually need the second statement, as your ordering will be the same after the delete, and synthesizing order numbers without gaps is easy enough (as shown above) without having to do mass updates.

Yes, the above solution is right. - [)ia6l0 iii replied to shekhar kumar on 29-Oct-09 01:18 PM

And if you are sending the parameters as XML, then create a Ntext input field , and then read the elements of the xml into a temp table and do a join as suggested. 

The below sample snippet should throw my light on what i meant.

CREATE PROCEDURE [dbo].[procedureName]
(
@xmlData NTEXT
)
--get a NText XML
DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData

--read it into a temp table.
SELECT * INTO #MailInfo FROM OPENXML (@idoc, ''/xmlTag/xmlAttribute'',2)
WITH (var1  VARCHAR(100),
var2 VARCHAR(1000),
var3 VARCHAR(255))

--and then do your actual select using a join.
SELECT alias.column1, .. FROM tablename alias
INNER JOIN #MailInfo mi ON alias.primaryKey = mi.primaryKey 

Thanks - shekhar kumar replied to Jonathan VH on 30-Oct-09 01:39 AM

Thanks for the solution.

"The better way is to create and populate a temporary table in your front-end code, and then join to that table in the SQL statement(s) in your stored procedure."

How can I create and pass a temporary table from the front end (C#)  in the store procedure, I am using C#.
I am a bit new in this type of code.

I want to do the following

The Channel_Order is the display order of the Links on the page. 
There are condition on display order 
  •  If it is 0 it will not be displayed on the page.
  •  If I delete a channel the others  Order should be altered accordingly only if the deleted channle_order is greater than 0. Suppose the user deletes the channel having channel_order 1 then all the channel having channle_order >1 should be decreased by one and so on...
  •  Multiple channel can have their order 0.
  • And Should be able to delete multiple channels.
Thanks a lot


Jonathan VH replied to shekhar kumar on 30-Oct-09 09:52 AM

To create a local temporary table, just execute a DDL command:

SqlConnection conn = new SqlConnection(sqlConnectionString);
string sql = @"IF OBJECT_ID('tempdb..#List') IS NOT NULL TRUNCATE TABLE #List ELSE
                       CREATE TABLE #List(Channel_Key int PRIMARY KEY)"
SqlCommand cmdDDL = new SqlCommand(sql, conn);
cmdDDL.ExecuteNonQuery();

Then populate the table. I typically create a simple stored procedure to do the population:

CREATE PROC dbo.FillList @Channel_Key int AS
SET NOCOUNT ON;
INSERT #List(Channel_Key)
VALUES(@Channel_Key);

In C#, execute that in your loop to insert a row for each value, and then execute the SP to delete the channels:

SqlCommand cmdFillList = new SqlCommand("dbo.FillList", conn);
cmdFillList.CommandType = CommandType.StoredProcedure;
cmdFillList.Parameters.Add("@Channel_Key", SqlDbType.Int);

foreach (int channelKey in channelDeletes)
{
  cmdFillList.Parameters["@Channel_Key"].Value = channelKey;
  cmdFillList.ExecuteNonQuery();
}

SqlCommand cmdDeleteChannels = new SqlCommand("dbo.DeleteChannels", conn);
cmdDeleteChannels.CommandType = CommandType.StoredProcedure;
cmdDeleteChannels.ExecuteNonQuery();

Note that the stored procedure that does the work takes no parameters. Because all your SQL commands share the same connection, the local temporary table is visible (in scope) to them but not to other connections.

From your narrative, I don't believe you need to change the Channel_Order values after the delete. As I implied in my earlier reply, one shouldn't do unnecessary updates in SQL, particularly if they could affect many rows. Your display order will be unchanged if you leave the gaps in the Channel_Order column. So my recommendation is to use just a stored procedure like this:

CREATE PROC dbo.DeleteChannels AS
SET NOCOUNT ON;
DELETE c
FROM dbo.Channel c JOIN #List t ON c.Channel_Key = t.Channel_Key;

If you have a legitimate reason for removing the gaps (and, again, you haven't provided one), then just use what I already suggested but exclude the rows with zero Channel_Order:

CREATE PROC dbo.DeleteChannels AS
SET NOCOUNT ON;
BEGIN TRAN;
DELETE c
FROM dbo.Channel c JOIN #Values t ON c.Channel_Key = t.Channel_Key;
WITH cte AS
(SELECT Channel_Key, ROW_NUMBER() OVER (ORDER BY Channel_Order) AS NewOrder
 FROM dbo.Channel
 WHERE Channel_Order > 0)
UPDATE cte SET Channel_Order = NewOrder
WHERE Channel_Order > NewOrder;
COMMIT TRAN;

Thanks a lot - shekhar kumar replied to Jonathan VH on 30-Oct-09 09:56 AM
Thanks a lot for the solution.