Can someone please help with this SQL 2005 stored proc?

Question

I am having trouble with a stored proc (SQL 2005).

I have a table called tbrm_Tags with two columns, TagID and TagName. I want to pass a TagName value to the stored proc and then I want to :

1) Check if the Tagname exists and if it does return the TagID

2) If the Tagname does not exist I want it to insert into the table and return the TagID.

Here is the stored proc I am using:

@TagID int = null,
@TagName varchar(50)    

AS

DECLARE @returnValue int

 BEGIN
 IF EXISTS (SELECT * FROM tbrm_Tags WHERE TagName = @TagName)
 BEGIN

SELECT

TagID

FROM tbrm_Tags

WHERE TagName = @TagName


END
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM tbrm_Tags WHERE TagName = @TagName)

 INSERT INTO tbrm_Tags

 (
  TagName

 )
 VALUES
 (
  @TagName

    )



 SELECT @returnValue = @@IDENTITY

 END
END

RETURN @returnValue

I cannot get the select statement to return the TagID when the Tagname exists. Any help would be appreciated.

Answer 1

SELECT

@returnValue = TagID

FROM tbrm_Tags

WHERE TagName = @TagName

Answer 2

Note: don't use @@IDENTITY - it is subject to triggers; always use SCOPE_IDENTITY()

I might just do:

DECLARE @returnValue int

SELECT @returnValue = TagID
FROM tbrm_Tags
WHERE TagName = @TagName

IF @returnValue IS NULL
BEGIN
     INSERT tbrm_Tags(TagName)
     VALUES (@TagName)

     SET @returnValue = SCOPE_IDENTITY()
END

RETURN @returnValue

Answer 3

I suspect Marc G.'s code would work just fine, but I would have made the last line like this:

SELECT @returnValue AS RETURNVALUE

I've always done it that way, but I am not sure of there is any functional difference between the two methods...anyone know?