Search code examples
sqlsql-serverinserttransactionsprocedures

Insert transaction if not exists in SQL Server


I want to insert in the table only if I do not have the specific column it is a track table with different titles and I want to add different titles with one procedure I did the try catch and I have problem in my where not exists condition, I don't know where to put the values term:

BEGIN TRANSACTION [Tran1]
BEGIN TRY

INSERT INTO  [s15guest59].[dbo].[track](track_topic)
values(@par1)
   WHERE NOT EXISTS (SELECT track_topic FROM [track]
                     WHERE track_topic=@par1)
COMMIT TRANSACTION [Tran1]
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION [Tran1]
END CATCH  

GO

I don't know what to write in the middle for it to work

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@par1".


Solution

  • if you planing to use stored procedure you can create stored procedure:

        CREATE PROCEDURE spAddTrack 
    @trackName VARCHAR(MAX)
    
    AS
    
    If not exists( Select * from [s15guest59].[dbo].[track] where track_topic=@trackName)
    BEGIN
        INSERT INTO  [s15guest59].[dbo].[track](track_topic) VALUES (@trackName)
    END
    

    thank execute it with:

    EXEC spAddTrack 'this is new track'