Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: Stored Procedure null fields problem

    Hello I would really appreciate it if someone could point me in the right direction.

    I have a stored procedure that inserts an article into a table named "articles" and at the same time takes that articles Primary Key and adds up to three related Images into a "Image" table.

    The problem I am having is if less than three images are entered, and there is no value/Image to enter for one of the insert statements the procedure is defaulting to try and insert an nvarchar into an image field and consequently failing.

    I know I could solve the problem by possibly having more than one procedure and using an if statement but was really hoping there might be a way I could check if there is anything to insert and insert and if not disregard that insert statement.

    Thanks.

    Code:
    ALTER PROCEDURE dbo.Insert_new_article
    @AuthorId int,
    @SectionName varchar(50),
    @CategoryName varchar(50),
    @ArticleTitle varchar(50),
    @Headline varchar(50),
    @Byline varchar(MAX),
    @Summary varchar(MAX),
    @Story varchar(MAX),
    @ArticleImage image,
    @ImageTitle varchar(50),
    @ArticleImage2 image,
    @ImageTitle2 varchar(50),
    @ArticleImage3 image,
    @ImageTitle3 varchar(50)
    AS
     Begin
     
      Set Nocount On
      
      DECLARE @ArticleId int
      
      Insert into Articles (AuthorId, SectionName, CategoryName, ArticleTitle, Headline, Byline, Summary, Story) 
    Values (@AuthorId, @SectionName, @CategoryName, @ArticleTitle, @Headline, @Byline, @Summary, @Story)
      
      SELECT @ArticleId=@@IDENTITY
      
      
      INSERT INTO Images(ArticleId, ArticleImage, ImageTitle)
      VALUES (@ArticleId, @ArticleImage, @ImageTitle)
      
      INSERT INTO Images(ArticleId, ArticleImage, ImageTitle)
      VALUES (@ArticleId, @ArticleImage2, @ImageTitle2)
      
      INSERT INTO Images(ArticleId, ArticleImage, ImageTitle)
      VALUES (@ArticleId, @ArticleImage3, @ImageTitle3)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ...
    INSER INTO dbo.images (articleid, articleimage, imagetitle)
    SELECT @articleid
         , @articleimage
         , @imagetitle
    WHERE  @articleid IS NOT NULL
    ...
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2009
    Posts
    51
    Quote Originally Posted by gvee View Post
    Code:
    ...
    INSER INTO dbo.images (articleid, articleimage, imagetitle)
    SELECT @articleid
         , @articleimage
         , @imagetitle
    WHERE  @articleid IS NOT NULL
    ...
    pretty good solution would it be
    WHERE @articleimage IS NOT NULL?
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •