Tuesday, February 14, 2012

User Defined Table Type in Sql Server

Find the solution of following queries:
How to pass collection[List/Array] in a Sql parameter?
How to Implement User Defined Table Type in Sql Server?
How can we use sql table as parameter in stored procedure?
How to pass multiple values in a single Sql parameter?
Suppose I have a table [dbo.Statistics] as following

ItemType                                ItemId                               ViewCount
1                                                     101                                          10
1                                                     102                                           4
1                                                     103                                           
2                                                     101                                           3
2                                                     103                                           11
2                                                     104                                           1                                                   

now I want to update the column ViewCount value with +1  for all the items comes in search result.
suppose for any condition two items with ItemId 101,103 and ItemType 1 displayed.  then after updation the table will be:
ItemType                               ItemId                            ViewCount
1                                                     101                                          11
1                                                     102                                           4
1                                                     103                                           1
2                                                     101                                           3
2                                                     103                                           11
2                                                     104                                           1   

This kinds of situations can be solved by using a parameter of type Table [User Defined Data Type]. This parameter can holds multiple ItemIds for them we can update ViewCount by +1.

Step by Step description of complete process:
Step-1: Create this table using:
CREATE TABLE [dbo].[Statistics]
(
    [ItemType] [TINYINT] NOT NULL,
    [ItemId] [INT] NOT NULL,
    [ViewCount ] [INT] NOT NULL
)


Step-2: Now create dbo.IntegerListTable as User defined dataType 'Table'
CREATE TYPE dbo.IntegerListTable AS TABLE
(
    id INT
)
Step-3: Now create a stored procedure to update ViewCount columns value that uses the User Defined Table Type
CREATE PROCEDURE [dbo].[CountMultipleViews]
    @ItemType TINYINT,
    @ItemIDs dbo.IntegerListTable READONLY,                
    @ViewCount INT = 1,
AS
BEGIN
    MERGE dbo.Statistics AS target
    USING @ItemIDs AS source    
        ON (target.ItemId = source.id  AND target.ItemType = @ItemType)
    WHEN MATCHED THEN
           UPDATE SET ViewCount = ViewCount +@ViewCount
    WHEN NOT MATCHED THEN   
           INSERT (ItemType, ItemId)  VALUES (@ItemType, source.id)
    OUTPUT inserted.*;
END;


Fore more details and .Net implementation read my next post[will come soon]

0 comments:

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Powered by Code Imagine