任意方式自动添加no是表

Question

这些是表格和编码

create table student
(
s_id varchar(10) unique,
s_name varchar(50),
s_report varchar(50),
)
create table student_contact
(
stid int identity primary key,
stcellphone varchar(50) unique,
s_id varchar(10),
foreign key(s_id) references student(s_id)
)
create table test
(
t_id int identity primary key,
t_marks int,
)
create table st_test
(
stt_id int identity primary key,
s_id varchar(10),
t_id int,
foreign key(s_id) references student(s_id),
foreign key(t_id) references test(t_id)
)

alter view [join]
as
select s1.s_name,s2.stcellphone,s4.t_marks,s1.s_report
from
student as s1
inner join
student_contact as s2
ON s1.s_id=s2.s_id
inner join
st_test s3
ON s1.s_id=s3.s_id
inner join
test as s4
ON s4.t_id=s3.t_id
alter procedure marks
@st_id varchar(10)='',
@name varchar(50)='',
@mobile varchar(50)='',
@marks int=''
as
begin
    if(@marks < 3)
    begin
    insert into student values(@st_id,@name,'You are enrolled for 6 months')
    insert into test values(@marks)
    insert into student_contact values(@mobile,@st_id)
    select * from [join] where s_name =@name
    end
end

这个编码,我创造了我已经创建了一个过程来插入数据现在我要"st_test"表中有2列名称(s_id t_id)引用学生表和测试现在的问题是我已经创建了一个视图,这些表加入,这一观点已经是程序问题是在程序中使用,我希望,当我填满所有的参数插入表根据条件。

Answer 1

你需要考虑使用SCOPE_IDENTITY():

declare @id int
insert into test values(@marks)
SET @id = SCOPE_IDENTITY()
insert into st_test values(@st_id,@id)
insert into student_contact values(@mobile,@st_id)
select * from [join] where s_name =@name

我还建议在你的最后一个选择语句中使用@id而不是s_name，因为你知道它应该是唯一的。