CODEWITHSUNDEEP
sqlsql-server-2008
user4221591
user4221591

Reputation: 2150

check if the combination of values from different fields in a table exists or not

I am writing a stored procedure where I need to check some constraints before inserting the value.

the combination of name, sex and HQ_code should not exists in the table. I am using select 1 for this constraint check. If the combination is not present then only execute the insert statement and if the combination already exists print message. Please see the comment part in the code. But can not proceed further.

create proc_set_ref_staff
    @name       varchar(50),
    @sex        varchar(1),
    @hq_code    varchar(4),
    @out_result varchar(500)   output

as
begin
    begin try

        select 1 from tbl_ref_staff where name = @name and sex = @sex and hq_code = @hq_code;
        --- if the combination exists set @out_result = 'already exists'

        --- how can I write if the above combination not exists then only execute insert statement
        --- insert into tbl_ref_staff values (@name, @sex, @hq_code)

    end try
    begin catch
        set @out_result = error_message();
    end catch;

end

Upvotes: 0

Views: 818

Answers (2)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

Maybe you are looking something like that?

 IF NOT EXISTS( SELECT 1 FROM tbl_ref_staff WHERE name = @name AND sex = @sex AND hq_code = @hq_code;)
 BEGIN
 INSERT INTO tbl_ref_staff VALUES (@name, @sex, @hq_code)
 END

Upvotes: 2

Kirill Slatin
Kirill Slatin

Reputation: 6143

You can use 

if exists(select 1 from ...) 
begin 
...
end

or you can use syntax

if (select count(1) from ..) > 0 
begin 
...
end

Upvotes: 1

Related Questions