How to write a "tagging" query in SQL?

Question

I am adding "Tagging" functionality in my web application. The table structures of my applications are as follows:

Tag:

(TagId INT IDENTITY, TagName VARCHAR(60))

TaggedRecords:

(TaggedId INT IDENTITY, TagId, TaggedRecordId)

Now, when I add a tag to any record, the next action should be performed using a single sql query or using a stored procedure;

• Is the tag already in the tag table or not?
• If a tag is present, it inserts a row into the "TaggedRecords" table
• If the tag is missing, first insert the tag into the "Tag" table, and then get the identifier of the newly added tag and insert the record into the "TaggedRecord" table.

, sql. If-Else sql.

,

Answer 1

...

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

, :)

OUTPUT .

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

( , , - " "!). , EXISTS , , EXISTS, SELECT.

, - :)

Answer 2

, , . - -SQL- :

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

, if clauses, ...

---

TagId , , MSSQL:

• OUTPUT:

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

• , :

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

, , IF EXISTS, :

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

, ? if/else?:)

, , 1 IF.

Answer 4

, . Oracle, , :

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 of the more functional permissions in the MERGE statement, so you can probably do something with the WHERE MATCHED clause (insert into the taggedrecords table).

Check Out MSDN Link