如何在存储过程中获取外键

问题描述

我有2桌艺术和艺术家。我编写了艺术存储过程，其中artistId是外键。在表格中插入数据的程序如下：

I have 2 tables arts and artist. I have written stored procedure for arts in which artistId is a foreign key. procedure for inserting data in table arts is as follows:

create procedure spInsertArts(
@artsId int out,
@name varchar(50),
@category varchar(50),
@artistId int
)
as
     begin
	select @artistId=SCOPE_IDENTITY()
		if exists(select artistId from artist)
         insert into arts(name,category,artistId) values(@name,@category,@artistId)
      end

当我执行它时，它给出了错误：过程或函数'spInsertArts'需要参数'@artistId'，这是未提供的。我想将artistId设置为artistId中艺术家的最新值Table.pls帮助。

when i execute it,it is giving error as:Procedure or function 'spInsertArts' expects parameter '@artistId', which was not supplied.i want to set artistId to latest value of artistId in artist Table.pls help.

推荐答案

SELECT @artsID = max(artID) from arts

（假设这是列名）你使用它可能是明智的在子查询中，取决于你如何安排事情。



现在，在插入之后，在你关闭连接之前，你可以使用

(assuming that's the column name) It may be sensible for you to use this in a subquery, depending upon how you arrange things.

Now, after an insert, and before you close the connection, you can use

SELECT TOP 1 @@IDENTITY From arts

，它将从您的存储过程中返回。 />


不要忘记在您的程序或退货记录中包含

and it would be returned from your stored procedure.

Do not forget to include

SET NOCOUNT ON

设置将是一团糟

create procedure spInsertArts(
@artsId int out=null,
@name varchar(50)=null,
@category varchar(50)=null,
@artistId int<pre>=null
)
as
     begin
	select @artistId=SCOPE_IDENTITY()
		if exists(select artistId from artist)
         insert into arts(name,category,artistId) values(@name,@category,@artistId)
      end

这篇关于如何在存储过程中获取外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！