如果reocrd在另一个表上为null，则插入到一个表中 [英] To insert to one table if a reocrd is null on another

问题描述

您好我的存储过程有问题，我想插入三个表，我创建了一个存储的procdure来做到这一点，但我有这两个表名为wishlist和general。我想插入wishlist表，如果dateaquired行为null但我创建的脚本无论如何插入到表中，有人可以改进我的脚本，以便它不会插入到我的wishlist表中，如果我的日期表中的dateaquired行不是空的

提前感谢



USE [MediaPlayer]

GO

/ ******对象：StoredProcedure [dbo]。[CreateBooks]脚本日期：12/03/2013 19:05:29 ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Hi I have a problem with my stored procedure, I want to insert into three tables which I created a stored procdure to do that, yet I have these two tables called wishlist and general. I want to insert into the wishlist table if the dateaquired row is null but the script I created inserts into the the table regardless, could someone please improve my script so that it does not insert into my wishlist table if my dateaquired row from my general table is not null
thanks in advance

USE [MediaPlayer]
GO
/****** Object: StoredProcedure [dbo].[CreateBooks] Script Date: 12/03/2013 19:05:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CreateBooks]

	-- Add the parameters for the stored procedure here
	@Name nvarchar (250),
	@FileName nvarchar (250),
	@FilePath  nvarchar (50),
	@FileSize float,
	@DateAdded date,
	@MediaLength nvarchar (50),
	@MediaSubType nvarchar(50),
	@MediaType nvarchar(50),
	@Thumbnail image,
	@DateAquired nvarchar(50),
	@BooksName nvarchar (50),
	@Publisher nvarchar(50),
	@Author nvarchar(50),
	@YearOfPublication date, 
	@Genre nvarchar(50),
	@ISBN nvarchar (50),
	@Synoposis nvarchar(50),
	@SeriesTitle nvarchar(50),
	@SeriesNumber nvarchar(50),
	@GeneralID int output,
	
	@BookID int output,
	@WishListID int output
	
AS
BEGIN
Insert into dbo.General
(Name, FileName, FilePath, FileSize, DateAdded, MediaLength,
MediaSubType, MediaType, Thumbnail, DateAquired)
values (@Name, @FileName, @FilePath, @FileSize, @DateAdded, @MediaLength, 
@MediaSubType, @MediaType, @Thumbnail, @DateAquired)
	
SET @GeneralID = @@IDENTITY 	
insert into dbo.Book(GeneralID, BooksName, Publisher, Author, [Year of publication], Genre,
ISBN, Synoposis,[Series Title],[Series Number])
Values (IDENT_CURRENT('dbo.General'), @BooksName, @Publisher, @Author, @YearOfPublication,  @Genre, @ISBN, @Synoposis, @SeriesTitle, @SeriesNumber)

SET @BookID = @@IDENTITY

Select GeneralID, Name, FileName,FilePath,FileSize,DateAdded,MediaLength,MediaSubType,  MediaType, Thumbnail,DateAquired As Wishlist   From General where NULLIF(DateAquired,'')IS Null 

Select * from WishLists

select GeneralID, MediaSubType, Name
From General where NOT EXISTS (Select Name from WishLists Where Name =@Name);

insert into Wishlists (generalID ,MediaType, Name)
values ((IDENT_CURRENT('dbo.General')),@MediaSubType, @Name)
SET @WishListID = @@IDENTITY
select * from wishlists

END

推荐答案

你可以使用IF EXSISTS

You could use IF EXSISTS

IF EXISTS (SELECT ... (whatever you need to look for)
  BEGIN
   -- Do your insert
  END 

这篇关于如果reocrd在另一个表上为null，则插入到一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！