Having an issue with a stored procedure SQL-Server

I’ve got a stored procedure that i’m having some issues with.

I’m trying to lookup against my table GOTWVotes and if VotedBy hasn’t voted before write the vote to the table(this is working) however if VotedBy has voted before not to write to the table and return VoteCount as 1.

Although it doesn’t write to the table when VotedBy exists the value of VoteCountalways appears to be 0

Any help would be appreciated

CREATE PROCEDURE [dbo].[Votes]
    @VotedMember BIGINT,
    @VotedBy BIGINT

    AS
    DECLARE @votecount INT

    BEGIN TRY
    BEGIN TRANSACTION t_Transaction
    SELECT TOP 1 * FROM [GOTWVotes] WITH (TABLOCKX)

    SELECT @votecount = COUNT(*) FROM [dbo].[GOTWVotes] 
    WHERE [VotedBy] = @VotedBy
    IF @votecount = 0

    INSERT INTO 
        [dbo].[GOTWVotes] ([VotedMember],[VotedBy])
    VALUES
        (@VotedMember, @VotedBy)
    COMMIT TRANSACTION t_Transaction
    END TRY
    BEGIN CATCH
        SET @votecount = -1
        ROLLBACK TRANSACTION t_Transaction
    END CATCH
        RETURN @votecount

Answer

You can do following code

CREATE PROCEDURE [dbo].[Votes]
    @VotedMember BIGINT,
    @VotedBy BIGINT,
    @votecount INT OUTPUT

    AS

    BEGIN TRY
    BEGIN TRANSACTION t_Transaction

    SET @votecount = 0
    IF NOT EXISTS(SELECT 1 FROM [dbo].[GOTWVotes] 
    WHERE [VotedBy] = @VotedBy)
    BEGIN
        INSERT INTO 
        [dbo].[GOTWVotes] ([VotedMember],[VotedBy])
    VALUES
        (@VotedMember, @VotedBy)        
    END
    ELSE
    BEGIN
        SELECT @votecount = COUNT(*) FROM [dbo].[GOTWVotes] 
    WHERE [VotedBy] = @VotedBy
    END


    COMMIT TRANSACTION t_Transaction
    END TRY
    BEGIN CATCH
        SET @votecount = -1
        ROLLBACK TRANSACTION t_Transaction
    END CATCH

To Call above Stored procedure you need to write following code

DECLARE @votecount int
EXEC dbo.Votes @VotedMember = 0, -- bigint
    @VotedBy = 0, -- bigint
    @votecount = @votecount OUTPUT -- int

SELECT @votecount