How to write "Tagging" query in SQL?

Question

Hi,

I am adding "Tagging" functionality in my web app. My applications table structures are as following;

Tag:

(TagId INT IDENTITY, TagName VARCHAR(60))

TaggedRecords:

(TaggedId INT IDENTITY, TagId, TaggedRecordId)

Now, I want when anyone adds a tag to any record then following action should be performed using a single sql query or using a stored procedure;

• The tag is already present in "Tag" table or not?
• If the tag is present then it inserts a row in "TaggedRecords" table
• Else if the tag is not present then first insert the tag in "Tag" table and then get the Id of newly added tag and insert a record in "TaggedRecord" table

Basically, I am more interested in doing these actions using a single query or at max two sql queries. I don't wanna make multiple If-Else conditions in sql stored procedure.

Thanks,

Answer 1

May I ask why? Is there a limit on if/else? :)

Looking at your case, it doesn't look like it will need more than 1 IF clause.

Answer 2

You've basically got it, but you can make it a little more efficient by changing the order. Here's some pseudo-SQL-code for your procedure:

SELECT TagId FROM Tag WHERE TagName = @NewTag into @TagId

IF @TagId IS NULL THEN
    INSERT new tag, returning the new TagId into @TagId

INSERT new record into TaggedRecords

Not sure why you're so adverse to using if clauses though...

---

To get the newly generated TagId into the variable, we have two options since you're using MSSQL:

• Use the OUTPUT command:

    INSERT INTO Tag (TagName) 
    OUTPUT Inserted.TagId INTO @TagId 
    VALUES (@NewTag);

• The tags are unique, so you should be able to just SELECT it back out:

    INSERT INTO Tag (TagName) VALUES (@NewTag);
    SELECT TagId FROM Tag WHERE TagName = @NewTag INTO @TagId;

If you want to SELECT it back out, the procedure might read better if you use an IF EXISTS clause, but essentially you're doing the same work either way:

IF NOT EXISTS(SELECT TagId FROM Tag WHERE TagName = @NewTag)
BEGIN
    INSERT INTO Tag (TagName) VALUES (@NewTag);
    SET @TagId = (SELECT TagId FROM Tag WHERE TagName = @NewTag);
END
ELSE
BEGIN
    SET @TagId = (SELECT TagId FROM Tag WHERE TagName = @NewTag);
END

INSERT new record into TaggedRecords

Answer 3

How about this...

CREATE PROC TagMe(@TagName VARCHAR(100),@TaggedRecordId INT)
AS

DECLARE @TagId INT

SET @TagId = SELECT TagId FROM Tag WHERE TagName = @TagName

IF @TagId IS NOT NULL
    BEGIN
    --Tag exists
        INSERT INTO TaggedRecords (TagId, TaggedRecordId) VALUES(@TagId,@TaggedRecordId)
    RETURN
    END
ELSE
    -- New tag
    BEGIN
        INSERT INTO Tag (TagName) OUTPUT inserted.id INTO @TagId VALUES(@TagName)
        INSERT INTO TaggedRecords (TagId, TaggedRecordId) VALUES(@TagId,@TaggedRecordId)
    RETURN
END

Not tested this but the theory should be sound :)

For another example of the OUTPUT usage see my post at this link

EDIT

As per comments below this version uses EXISTS...

CREATE PROC TagMe(@TagName VARCHAR(100),@TaggedRecordId INT)
AS

DECLARE @TagId INT

IF EXISTS(SELECT TagId FROM Tag WHERE TagName = @TagName)
    BEGIN
    --Tag exists
        SET @TagId = SELECT TagId FROM Tag WHERE TagName = @TagName  
        INSERT INTO TaggedRecords (TagId, TaggedRecordId) VALUES(@TagId,@TaggedRecordId)
    RETURN
    END
ELSE
    -- New tag
    BEGIN
        INSERT INTO Tag (TagName) OUTPUT inserted.id INTO @TagId VALUES(@TagName)
        INSERT INTO TaggedRecords (TagId, TaggedRecordId) VALUES(@TagId,@TaggedRecordId)
    RETURN
END

Although I'm not sure (I reliase I'm arguing against myself here but so much of this stuff is an "it depends" answer!). This example actually would work best for more usage of new tags because it would only perform the EXISTS once and then continue whereas for an existing tag it would perform an EXISTS and then a SELECT.

Hmm, take your pick - or test both approaches under volume :)

Answer 4

You use a merge into your tag table followed by an insert into your tagged records table. This is Oracle syntax, so you'll have to tweak it, but the idea is the same:

MERGE INTO TAG
USING (SELECT @tagname as TAGNAME FROM DUAL) RECORD
ON (TAG.TAGNAME = RECORD.TAGNAME)
WHEN NOT MATCHED THEN INSERT (TAG.TAGNAME) VALUES (@tagname);

INSERT INTO TAGGEDRECORDS(TAGID) VALUES (SELECT TAGID FROM TAG WHERE TAGNAME=@tagname);

I think SQL Server also supports some more functional power in the MERGE statement, so you could probably do something with a WHEN MATCHED (insert into the taggedrecords table) clause.

Check out MSDN link