SQL Error: Subquery returned more than 1 value

问题:

I'm getting keyword input in a textbox like test,check,..etc.I insert those keyword as individual rows in my database on checking if it exists,if exists update or else insert it.This keywordid and item that represents to this id should be inserted into bridge table.On inserting existing keyword it throws errors:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Cannot insert the value NULL into column 'KeywordId', table 'dbo.Keywordsitems'; column does not allow nulls. INSERT fails.

I'm using the below stored procedure

ALTER PROCEDURE [dbo].[InsertKeywords] 
(
  @keywordName nvarchar(256),
  @itemid uniqueidentifier
)
AS
begin
  declare 
    @itemid uniqueidentifier,
    @keywordid uniqueidentifier,
    @id uniqueidentifier;
  declare @keyworddata table (keywordid uniqueidentifier);

  set @id = (select KeywordId  from keywords where KeyName=@keywordName)
  set @itemid =(select itemId from Items where ItemID = @itemid);

  if not exists(select keyname from keywords where KeyName = @keywordName)
  begin
    insert into Keywords(KeywordId,KeyName)
    output inserted.KeywordId into @keyworddata(keywordid)
    values (newid(),@keywordName);

    select @keywordid = keywordid from @keyworddata;

    insert into Keywordsitems(KeywordId,ItemId)
    values (@keywordid,@itemid);
  end
  else
  begin
    update keywords set KeyName=@keywordName where KeywordName= @keywordName;
    insert into KeywordsResources(KeywordId,itemId) values (@id,@itemid);       
  end
end

回答1:

You can use TOP 1 to resolve this.

E.g.,

...
set @id = (select top 1 KeywordId  from keywords where KeyName=@keywordName)
set @itemid =(select top 1 resourcedatabaseId from Items where ItemID = @itemid); 
...