Recent Comments

Tags

Widgets



Comodo SSL Seal

Parsing A List Of Tags Using Stored Procedure (MS SQL)

Author → Asif → in MS-SQL → on May 31 2008 12:12AM

Tags: Tags StoredProcedure Tags MSSQL Tags Tags Tags dotnet Tags DB

On to the stored procedure ...
 
    DECLARE @TagName varchar(500), @Pos int
 
    SET @TagList = LTRIM(RTRIM(@OrderList))+ ',' 
    SET @Pos = CHARINDEX(',', @OrderList, 1)
 
    IF REPLACE(@OrderList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @TagName = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
            
            IF @TagName <> ''
            BEGIN
                INSERT INTO Tags VALUES (@TagName, @AID)
            END
            SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
            SET @Pos = CHARINDEX(',', @OrderList, 1)

        END
    END   
 
------------------------------------------------------------
 
SET @TagList = LTRIM(RTRIM(@List))+ ',' 
SET @Pos = CHARINDEX(',', @List, 1)
 
So, first we get our tags from @List which is inputted from our code and goes something like:
 
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.CommandText = "InsertTags";
......
 cmd.Parameters.Add("@List", SqlDbType.VarChar).Value = HttpUtility.HtmlEncode(Tags);
......
 
Next, a check
IF REPLACE(@OrderList, ',', '') <> ''
 
Then we want to initialize the loop with the start position (@Pos > 0) and trim white space:
SET @TagName = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
 
I'm also inserting the article or post ID with the tag hence @AID:
INSERT INTO Tags VALUES (@TagName, @AID)
I can do this when I want to save a new article and associated tags for that article all in one transaction.  When inserting a new article, just get the article ID by selecting the @@Identity before the first check (i.e. SELECT @AID = @@IDENTITY FROM Articles). If editing the article, just get the article ID from cmd.Parameters.
 
Next, reset the list using its length (after insert) and current position:
SET @List = RIGHT(@List, LEN(@List) - @Pos)
 
Finally, update the current position:
SET @Pos = CHARINDEX(',', @OrderList, 1)
 
So, there it is...a simple way to parse a tag list from a simple textbox delimited by commas. I actually use this method on this site.
 


 
Comments RSS

Leave a Comment

No Comments Yet