You could do this using an IF
statement:
IF NOT EXISTS ( SELECT 1 FROM tblSoftwareTitles WHERE Softwarename = @SoftwareName AND SoftwareSystemType = @Softwaretype ) BEGIN INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType) VALUES (@SoftwareName, @SoftwareType) END;
You could do it without IF
using SELECT
INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType) SELECT @SoftwareName,@SoftwareType WHERE NOT EXISTS ( SELECT 1 FROM tblSoftwareTitles WHERE Softwarename = @SoftwareName AND SoftwareSystemType = @Softwaretype );
Both methods are susceptible to a race condition, so while I would still use one of the above to insert, but you can safeguard duplicate inserts with a unique constraint:
CREATE UNIQUE NONCLUSTERED INDEX UQ_tblSoftwareTitles_Softwarename_SoftwareSystemType ON tblSoftwareTitles (SoftwareName, SoftwareSystemType);
ADDENDUM
In SQL Server 2008 or later you can use MERGE
with HOLDLOCK
to remove the chance of a race condition (which is still not a substitute for a unique constraint).
MERGE tblSoftwareTitles WITH (HOLDLOCK) AS t USING (VALUES (@SoftwareName, @SoftwareType)) AS s (SoftwareName, SoftwareSystemType) ON s.Softwarename = t.SoftwareName AND s.SoftwareSystemType = t.SoftwareSystemType WHEN NOT MATCHED BY TARGET THEN INSERT (SoftwareName, SoftwareSystemType) VALUES (s.SoftwareName, s.SoftwareSystemType);