Table Type Parameter in SQL SERVER 2008

We have problem to insert multi line data from websites or Grid into database.
Till SQL SERVER 2000 we will send calls for each row insertion to the procedure
In SQL SERVER 2005 there is a new data type XML which can be used to insert multiple data in one row. But we need to include XQuery to read that data first.
In SQL SERVER 2008 a new parameter type Table Parameter introduce which can be used to pass parameter from code to database.
to do this we need to perform following
1. Create a User defined table type

/****** Object:  UserDefinedTableType [dbo].[CommentsValues]    Script Date: 01/13/2011 10:59:07 ******/
CREATE TYPE [dbo].[CommentsValues] AS TABLE(
[CommentID] [int] NULL,
[Value] [bit] NULL,
[Comments] [varchar](max) NULL,
[CommentTypeId] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CommentTypeId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

2. Create Procedure using user define table type.

CREATE PROCEDURE [dbo].[up_insertUpdateComments]
-- Add the parameters for the stored procedure here
@PropID int,
@UserID int,
@pCommentsValues CommentsValues READONLY
AS
BEGIN
SET NOCOUNT ON;

Declare @tCommentsValues table ( [CommentID] int , [Value] bit , [Comments] text, [CommentTypeId] int)

Insert into @tCommentsValues ( [CommentID] , [Value] , [Comments] , [CommentTypeId] )
select (case when CommentID is null  then (row_number() over (order by [CommentTypeId]) + @CommentId) else commentid end) ,value,Comments,[CommentTypeId]
from @pCommentsValues


   select * from @tCommentsValues


END

3. Call from .Net Application

public datatable GetTable()

{

DataTable CustomTable = new DataTable();

CustomTable.Columns.Add(new DataColumn("Id", Type.GetType("System.Int32")));

CustomTable.Columns.Add(new DataColumn("CValue", Type.GetType("System.String")));

DataRow row = CustomTable.NewRow();

row[0] ="1";

 row[1] = "wajid";

 CustomTable.Rows.Add(row);

 row[0] ="2";

 row[1] = "shamas";

 CustomTable.Rows.Add(row);

row[0] ="3";

 row[1] = "sheraz";

 CustomTable.Rows.Add(row);

CustomTable.AcceptChanges();

return CustomTable;

}

Merge Statement SQL SERVER 2008

    Using Merge Statement we can apply Insert, Update and Delete in a single statement

Following is the example for Merge Statement

; MERGE INTO comments AS PC
USING ( select [CommentID] , [Value] , [Comments] , [CommentTypeId]
from @tCommentsValues) as p
on PC.CommentID = p.CommentiD
WHEN MATCHED THEN
UPDATE SET Comments = p.comments,
DateModified = getdate(),
ModifiedBy = @UserID
WHEN NOT MATCHED BY TARGET THEN
 INSERT(CommentId,Comments,DateCreated,DateModified,Active,Deleted,CommentTypeId)
 VALUES (p.CommentID ,p.Comments ,getdate(),@UserID,1,0,[CommentTypeId])

Finding Table or Column usage in Objects (Table, Views, Functions, Procedures)

select * from sys.objects where object_id in (select id from syscomments where text like '%merge%')

It will find Merge word from all system objects.