inserting multiple insert statement questions

Question

Post reply

inserting multiple insert statement questions

Patrick.I

Hall of Fame

Points: 3105
More actions
June 8, 2006 at 6:12 pm

#114397

Hi guys,
      I have multiple insert statements below
what i want is if "ProductName1" or "ProductName2" etc .. field is null then i don't want the
insert statement to execute
@ProductName nvarchar(40)
INSERT INTO Products (ProductName1 UnitPrice1
VALUES (@ProductName1 @UnitPrice1
INSERT INTO Products2 (ProductName2, UnitPrice2)
VALUES (@ProductName2, @UnitPrice2)
INSERT INTO Products3 (ProductName3, UnitPrice3)
VALUES (@ProductName3,@UnitPrice3)
INSERT INTO Products4 (ProductName4, UnitPrice4)
VALUES (@ProductName4,@UnitPrice4)
INSERT INTO Products5 (ProductName5, UnitPrice5)
VALUES (@ProductName5,@UnitPrice5)
Can i do :-
IF @ProductName = null
    BEGIN

    END
ELSE
    BEGIN
     INSERT INTO Products (ProductName1 UnitPrice1
VALUES (@ProductName1 @UnitPrice1
END
Any ideas whats the best way to tackle this?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

This:

IF @ProductName = null

Should read:

IF @ProductName is NULL

stax68 SSChampion Points: 11711 More actions · Answer 2

if @ProductName is not null
  insert Products(ProductName,UnitPrice)
  VALUES (@ProductName, @UnitPrice)

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Patrick.I Hall of Fame Points: 3105 More actions · Answer 3

Thanks.

But the thing is i have 6 insert statements FOR example below

So can i do :-

if @ProductName,@ProductName1 is not null

insert Products(ProductName,UnitPrice)

VALUES (@ProductName, @UnitPrice)

insert Products(ProductName,UnitPrice)

VALUES (@ProductName1, @UnitPrice)

insert Products(ProductName,UnitPrice)

VALUES (@ProductName2, @UnitPrice)

insert Products(ProductName3,UnitPrice)

VALUES (@ProductName4, @UnitPrice)

insert Products(ProductName,UnitPrice)

VALUES (@ProductName5, @UnitPrice)

insert Products(ProductName,UnitPrice)

VALUES (@ProductName6, @UnitPrice)

How can i check for the 6 parameters i'm interested in?

Mark Yelton SSCommitted Points: 1570 More actions · Answer 4

I think you're going to have to do the check for the value prior to each individual insert.

Mark

stax68 SSChampion Points: 11711 More actions · Answer 5

insert Products(ProductName,UnitPrice)
select V.pn, V.up from( select @ProductName pn, @UnitPrice up union all
select @ProductName2, @UnitPrice2 union all
select @ProductName3, @UnitPrice3 union all
select @ProductName4, @UnitPrice4 union all
select @ProductName5, @UnitPrice5 union all
select @ProductName6, @UnitPrice6) V
where V.pn is not null

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

DAVNovak Hall of Fame Points: 3120 More actions · Answer 6

Patrick,

Do you have multiple product tables or one product table with multible productname fields? From your first post to your second it changes.

Either way, I believe you will have to check each @productname# variable for a non null value: IF @ProductName# IS NOT NULL INSERT INTO Products? (..., ...) VALUES (..., ...).

Dave N

brendt hess Hall of Fame Points: 3256 More actions · Answer 7

I may have read your request incorrectly, but it seemed as if you were asking "If any of the six product names are null, don't insert any of them." If this is the case, then:

-- If any parameter is null, this will return NULL by default

-- NOTE: Settings on the server can affect this - TEST FIRST

If (@ProductName1 + @ProductName2 + @ProductName3 + @ProductName4 + @ProductName5 + @ProductName6) Is Not Null

BEGIN

... inserts here

END

If not, then your structure is more like:

IF @ProductName1 Is Not Null