Using exists properly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using exists properly

Author

Topic

rlull
Starting Member

39 Posts

Posted - 2006-07-19 : 11:53:52

I want to execute the follow sproc but I only want the insert to run if the record doesn't already exist. I've tried various combinations but can't figure out where to put the 'if not exists' operator. I know I'm missing something simple here. Thanks for any help.

CREATE procedure AddBOProductToCart
(@CartID char(36),
@ProdID int,
@Qty int,
@BasePrice smallmoney,
@SellingPrice smallmoney,
@ItemDiscount tinyint,
@CustID int,
@BackOrdered bit)
as
(select Name from Products where ProdID = @ProdID)
insert into Cart (CartID, ProdID, Qty, BasePrice, SellingPrice, ItemDiscount, DateProductAdded, CustID, BackOrdered)
values (@CartID, @ProdID,@Qty, @BasePrice, @SellingPrice, @ItemDiscount, getdate(), @CustID, @BackOrdered))
return
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-19 : 11:59:31

if not exists (select Name from Products where ProdID = @ProdID)
begin
-- your insert here
end

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-19 : 12:00:06

insert into Cart (CartID, ProdID, Qty, BasePrice, SellingPrice, ItemDiscount, DateProductAdded, CustID, BackOrdered)
select @CartID, @ProdID,@Qty, @BasePrice, @SellingPrice, @ItemDiscount, getdate(), @CustID, @BackOrdered
where not exists (select * from Cart where CartID = @CartID)

also
(select Name from Products where ProdID = @ProdID)
doesn't do anything

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rlull
Starting Member

39 Posts

Posted - 2006-07-19 : 12:16:00

Thanks, you were correct. I eliminated that line and used your suggestion. Works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources