Tech Off Thread

3 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL code used to work on SQL 2000, but doesn't work right on '05.

Back to Forum: Tech Off
  • User profile image
    qwert231

    This SQL stored procedure should insert a row into the ShoppingCart table, and I should never have a Null value in ImageOrderNum when it's done. But I do...

    Why?

    ALTER PROCEDURE [dbo].[CartInsert]
    @PhotographerID int,
    @ImageOrderNum int = Null,
    @OrderNum int = Null,
    @CreateDate datetime,
    @Category varchar(50) = '',
    @ImageFolder varchar(100) = '',
    @Reference varchar(30) = '',
    @Comments varchar(100) = '',
    @JobTypeID int = Null,
    @FilmID int = Null,
    @ShoppingCartID int = 0,
    @ProgramID char(4) = '',
    @OptionID int = null,
    @DatabaseFile varchar(60) = null,
    @UseDP2Images bit = 1,
    @Reorder bit = 0,
    @OrderName varchar(50) = null,
    @LinkedCartID int = null,
    @FilesSent varchar(12) = null,
    @Cropping bit = 0
    AS

    --Insert the values
    INSERT INTO ShoppingCart (
    PhotographerID,
    ImageOrderNum,
    OrderNum,
    CreateDate,
    Category,
    ImageFolder,
    Reference,
    Comments,
    JobTypeID,
    FilmID,
    ProgramID,
    OptionID,
    DatabaseFile,
    UseDP2Images,
    Reorder,
    OrderName,
    LinkedCartID,
    FilesSent,
    Cropping
    )
    VALUES (
    @PhotographerID,
    @ImageOrderNum,
    @OrderNum,
    GetDate(),
    @Category,
    @ImageFolder,
    @Reference,
    @Comments,
    @JobTypeID,
    @FilmID,
    @ProgramID,
    @OptionID,
    @DatabaseFile,
    @UseDP2Images,
    @Reorder,
    @OrderName,
    @LinkedCartID,
    @FilesSent,
    @Cropping
    )

    --Get the new Key
    SELECT @ShoppingCartID = @@IDENTITY

    If @ImageOrderNum IS Null AND @PhotographerID <> 999 AND @PhotographerID <> 99999
    BEGIN
    UPDATE ShoppingCart SET ImageOrderNum = @ShoppingCartID WHERE ShoppingCartID = @ShoppingCartID
    END

    If @ImageOrderNum = 0 AND @PhotographerID <> 999 AND @PhotographerID <> 99999
    BEGIN
    IF @JobTypeID IS NOT Null
      BEGIN
      UPDATE ShoppingCart SET ImageOrderNum = @ShoppingCartID WHERE ShoppingCartID = @ShoppingCartID
      END
    END

    --Return said key
    Return @ShoppingCartID

  • User profile image
    Maurits

    Do you have any triggers on the ShoppingCart table?

  • User profile image
    davearkley

    qwert231 wrote:
    This SQL stored procedure should insert a row into the ShoppingCart table, and I should never have a Null value in ImageOrderNum when it's done. But I do...

    Why?

    ALTER PROCEDURE [dbo].[CartInsert]
    @PhotographerID int,
    @ImageOrderNum int = Null,
    ^^ You set it Null here
    ...
    AS

    --Insert the values
    INSERT INTO ShoppingCart (
    @PhotographerID,
    @ImageOrderNum,
    ^^ could still be Null here
    ...
    )

    If @ImageOrderNum IS Null AND @PhotographerID <> 999 AND @PhotographerID <> 99999
    ^^ Will still be left Null if @PhotographerID =999 or 9999

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.