Another way to bulk update data in SQL (Updated)

In a small application, sometimes we need to update some among of data in database frequently. I ever mentioned how to convert xml into a table in SQL, so that we can convert those data into a XML file and send it to SQL server.
What if those data that need to be updated contain many duplicate columns? We can send those duplicate columns just once, and concatenate those non-duplicate columns into a string and send it to SQL as a parameter. It works like this:
1.create a method to fetch data from List and merge them into a string by using a delimiter to seperate them.
For example:

//This is a new feature of C# 3.0. Take a look of this:
//"http://blogs.msdn.com/abhinaba/archive/2005/09/17/470358.aspx".
List<int> CustomerIdList = new List<int>{1001,1002,1003,1004};
string CustomerIds = MergeData(CustomerIdList);

Create your own MergeData() method to merge the input List<int> and output as a single string like this: "1001,1002,1003,1004".
2.Send this string as a parameter to the SQL server.
3.At SQL server, we need to create a Table-Valued function to parse this string into a table.

CREATE FUNCTION [dbo].[fxnParseCommaDelmitedList]
(
@CommaDelimitedList varchar(8000)
)
RETURNS @TableVar TABLE (ItemID int NOT NULL )
AS
BEGIN
  DECLARE @IDListPosition int
  DECLARE @IDList varchar(4000)
  DECLARE @ArrValue varchar(4000)
  SET @IDList = COALESCE(@CommaDelimitedList, '')
  IF @IDList <> ''
  BEGIN
  -- Add comma to end of list so user doesn''t have to
  SET @IDList = @IDList + ','
  -- Loop through the comma demlimted string list
  WHILE PATINDEX('%,%' , @IDList ) <> 0
    BEGIN
      SELECT @IDListPosition = PATINDEX('%,%' , @IDList)
      SELECT @ArrValue = LEFT(@IDList, @IDListPosition - 1)
      -- Insert parsed ID into TableVar for "where in select"
      INSERT INTO @TableVar (ItemID) VALUES (CONVERT(int, @ArrValue))
      -- Remove processed string
      SELECT @IDList = STUFF(@IDList, 1, @IDListPosition, '')
    END
  END
  RETURN
END

This sproc will return a table like this:

ItemID (column name)
1001
1002
1003
1004

A lighter way than using XML file.

What's the different among Parse(), TryParse(), and ConvertTo() ?

These three methods are all converting data from one type to another, but why we have three ways (methods) for the same purpose? What's the reason for that? What's the differences? Here is a very good article for all these question: Performance Profiling Parse vs. TryParse vs. ConvertTo.
After reading this, I finally know what's the differences and when to use them. There are always people doing such a detail research for us. Thanks!

Convert xml to table in SQL 2005

This is how I convert XML string into a Table in SQL 2005:

DECLARE @tempTable TABLE (
userId INT,
userName NVARCHAR(50),
password NVARCHAR(50)
)
DECLARE @xml XML
SET @xml='
<row userId="67" userName="Kenny1" password="1234" />
<row userId="80" userName="Kenny2" password="5678" />'

INSERT INTO @tempTable
SELECT Tbl.Col.value('@userId', 'INT'),
       Tbl.Col.value('@userName', 'NVARCHAR(50)'),
       Tbl.Col.value('@password', 'NVARCHAR(50)')
FROM   @xml.nodes('//row') Tbl(Col)

--See the table
SELECT * FROM @tempTable 

Web page search and SQL

Here is a way to search many columns in a table:

CREATE PROCEDURE [dbo].[SearchExample] (
@userId UNIQUEIDENTIFIER = NULL,   --set all parameters' default value to null
@firstName NVARCHAR(50) = NULL,
@lastName NVARCHAR(50) = NULL,
@address NVARCHAR(100) = NULL,
@zipcode INT = NULL
)

SELECT ...  --omit
FROM   dbo.UserInfo u
WHERE  u.userId = COALESCE(@userId, u.userId)
AND    u.firstName LIKE ('%'+ COALESCE(@firstName, u.firstName)+'%')
AND    ...  --omit
...    --omit

Set the default value to null for all the input parameters is because if we don't have any filter to search (the value is null), then COALESCE() will use the second parameter as the output, otherwise it will output the first parameter.
For example, if @userId = 1001, the

WHERE  u.userId = COALESCE(@userId, u.userId)

will become like

WHERE  u.userId = 1001

Otherwise, it will become like

WHERE  u.userId = u.userId

Brainstorming: Prefix and Postfix Operators

Another interested code segment talks about prefix and postfix operators:

class IncrementExample
{
 public static void Main()
 {
   int x = 1;

   Console.WriteLine("{0}, {1}", x++, x++);
   Console.WriteLine("{0}, {1}", ++x, ++x);
 }
}

The result is

1, 2
4, 5

Since x++ has a postfix operator, that means x will give its value to Console.WriteLine first, then perform the ++ operation. In contrast, prefix operator will perform ++ operation first, then give the value to Console.WriteLine.