CODEWITHSUNDEEP
sql-serverstored-procedures
Ahalya
Ahalya

Reputation: 23

insert foreign key values in store procedure sql server

I've created a insert stored procedure with two tables like in the exapmle:

reference table :
    create table customerdetails (eid int,
    dsgid int Foreign Key Referencesdesg_d(d_id),
    ename varchar(90),
    dob datetime,addr varchar(100),pincode int,salary,int,dojdatetime)

   insert into customerdetails values (1,1,'ahalyaa','05.13.1993','chennai',600024,345,'06.02.2014')

source table:

create table desg_d(d_id int primary key,desg varchar(90))
 insert into desg_d values(1,'programmer')  
 insert into desg_d values(2,'manager')

my store procedure: 

    create procedure sp_i @iid int,@iname varchar(90),@idobdatetime,
     @iaddr varchar(100),@ipincode int, @isalary int,@iDoj datetime 
    as 
    begin 
        declare @idesg int
        set @idesg=1 
        insert into customerdetails(eid,dsgid,ename,dob,addr,pincode,salary,doj)
        values(@iid,@idesg,@iname,@idob,@iaddr,@ipincode,@isalary,@iDoj)
    end

if i give set=1,then always idesg value should be 1, but i need to insert idesg value randomly, pls help me.

Upvotes: 2

Views: 12767

Answers (2)

Tonned
Tonned

Reputation: 296

Foreign key values are also the same with the normal insert. The difference is that foreign key values to be inserted should exist on the main table.

Also, please reconsider on naming your variable in your stored procedure. Please see sample below.

create procedure sp_i
 @eid int
,@dsgid int
,@ename varchar(90)
,@dob datetime
,@addr varchar(100)
,@pincode int
, @salary int
,@Doj datetime 
as
begin 
declare @idesg int
insert into customerdetails
(eid,dsgid,ename,dob,addr,pincode,salary,doj)
values
(@eid,@dsgid,@ename,@dob,@addr,@pincode,@salary,@Doj)

end

Upvotes: 1

mehdi lotfi
mehdi lotfi

Reputation: 11571

Use following format in your stored procedure:

DECLARE @DesgId int
INSERT INTO Desg(COLUMN) VALUES(@VALUES)
SET @DesgId = SCOPE_IDENTITY()

INSERT INTO customerdetails ( ..., Dsgid, ...)
VALUES (..., @DesgId, ...)

You can also use following format:

INSERT INTO Desg(COLUMN) VALUES(@VALUES)

INSERT INTO customerdetails ( ..., Dsgid, ...)
VALUES (..., (Select Top(1) d_id from desg_d where desg = @Desg), ...)

Upvotes: 1

Related Questions