如何在存储过程中获取外键 [英] how to get foreign key in stored procedure

问题描述

我有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.

推荐答案

如果artsID是一个标识列（保证唯一性和单调性），你可以要求

If artsID is an identity column (guaranteeing uniqueness and monotonicity), you could ask for

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

设置将是一团糟

这样改变

change like this

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

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