IF response unexpected result in stored procedure, unique key violation

Question

I might have looked on this too long, so i hope someone can help me out here.

i'm playing around with comparing files metadata to identify unique data chunks, and thereby detect deduplication potential... here goes.

drop proc insertFile
go
create proc [dbo].[insertFile] @fileHash char(64), @name varchar(200)
as
set nocount on;
declare @fileId int
declare @klientId int
set @klientId = (SELECT cast(RAND() * 10 + 1 as int))


IF NOT EXISTS (select * from data_file where hash_key = '@fileHash')
begin
insert into data_file (hash_key) values (@fileHash)
end

set @fileId = (select id from data_file where hash_key = '@fileHash')
insert into klient_file (data_file, klient, name) values (@fileId, @klientId, @name)

there is a unique constraint on hash_key and this is violated when i enter a value that exists, this should not happen, the IF checks if it exist, and should only insert if the hash value does not.

the data should enter klient_file no matter what...

again, the error is the violation of the unique constraint, that should have been avoided with the IF check, the IF works by its own, just not in the procedure. any thoughts? (this all runs on a localdb instance)

Answer 1

You have your parameter within quote marks in the EXISTS check, so in this line

IF NOT EXISTS (select * from data_file where hash_key = '@fileHash')

you are checking if '@fileHash' exists, not the actual value assigned to the parameter, so even if the hash_key exists you are trying to insert it because '@FileHash' does not exist in the table

Your procedure should be:

create proc [dbo].[insertFile] @fileHash char(64), @name varchar(200)
as
set nocount on;
declare @fileId int
declare @klientId int
set @klientId = (SELECT cast(RAND() * 10 + 1 as int))


IF NOT EXISTS (select * from data_file where hash_key = @fileHash)
    begin
        insert into data_file (hash_key) values (@fileHash)
    end

set @fileId = (select id from data_file where hash_key = @fileHash)
insert into klient_file (data_file, klient, name) values (@fileId, @klientId, @name)