to insert into one table if record is null

Question

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.

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

score 0 · Answer 1 · edited May 23 '17 at 10:25

I could be wrong but to me it seems you are missing some logic cases.

Your code:

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

What this does is selects the named fields from General where DateAcquired is Null. On a side note, I believe (I may be wrong) that if you want all those fields AS Wishlist, you should have them all in parentheses like:

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

Regardless, once you have this data you aren't doing anything with it, you are moving into a new select query, selecting * from Wishlists. Then you are selecting from General again where the the name is not in the table already. Then you are inserting into Wishlists the current Identity value of dbo.General, @MediaSUbType, @Name.

So all this code is really doing is running some queries, then regardless of those results, is inserting the Identity value of dbo.General, and the status values @MediaSubType and @Name into the table wishlist. In order for all the queries you run before to have an affect on what gets inserted, you need to use cases.

I don't know enough about the tables and things to write up the code you will need, but this answer should give you an idea of how to use CASE WHEN to do what you need.

Hope this helps some and isn't just useless ramblings from me.

I understand what you on about , i'm new with sql , i'm still studying it and there few pointers I could use to help me out, thanks hy — user2936417, Dec 04 '13 at 06:50

score 0 · Answer 2 · answered Dec 03 '13 at 19:38

If you're just trying to skip the WishList insert based on the @DateAquired being null you can modify your script as below. Please reply if this is not what you want.

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 = scope_identity(); --use this instead of @@IDENTITY

    insert into dbo.Book(GeneralID, BooksName, Publisher, Author, [Year of publication], Genre, ISBN, Synoposis,[Series Title],[Series Number])
        Values (@GeneralID, @BooksName, @Publisher, @Author, @YearOfPublication, @Genre, @ISBN, @Synoposis, @SeriesTitle, @SeriesNumber)

    SET @BookID = scope_identity(); --use this instead of @@IDENTITY


    -- you already have the DateAquired from the insert into dbo.General above, so just use the param @DateAquired
    if(@DateAquired is null)
    begin

        insert into Wishlists (generalID, MediaType, Name)
        values (@GeneralId, @MediaSubType, @Name)

        SET @WishListID = scope_identity(); --use this instead of @@IDENTITY
    end


    select [InsertedGeneralId] = @GeneralID, [InsertedBookID] = @BookID, [InsertedWishListID] = @WishListID

END

hi Nathan its not inserting anything into the wishlist table whether the dateaquired is null or not — user2936417, Dec 04 '13 at 06:49
Post more code so we can see complete picture. There must be something else going on. Give us table structure and example stored proc inputs. — nathan_jr, Dec 05 '13 at 00:40

score 0 · Answer 3 · answered Dec 03 '13 at 19:45

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 = SCOPE_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 = SCOPE_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);

        IF (@DateAquired IS NULL)
        BEGIN
          insert into Wishlists (generalID ,MediaType, Name)
          values ((IDENT_CURRENT('dbo.General')),@MediaSubType, @Name)
          SET @WishListID = SCOPE_IDENTITY()
        END 

          select * from wishlists

END

Also use SCOPE_IDENTITY() instead of @@IDENTITY as it will return last generate Identity in that column , even if it was created by a concurrent connection.

to insert into one table if record is null

3 Answers3