Biztalk 2004, one stored procedure for 2 tables

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

General SQL Server Forums

New to SQL Server Programming

Biztalk 2004, one stored procedure for 2 tables

Author

Topic

prettypixie
Starting Member

8 Posts

Posted - 2006-12-15 : 11:13:48

Hi,

I have an orchestration that recieves a pricelist flat file and it must insert into 2 tables - article, articlePrice(for each LotSize) into sql server using a sql adapter. I also want it to check if a record exists for that lotSize and update it if it does. The records are indentified with a uniqueidentifier. So I have 3 if statements:
1 - if (<the article does not exist(checks SupplierArticleNumber)>)
<insert a record into article table>
<insert a record into articlePrice table>

2 - else if(<the article exists but not for that lotSize>)
<insert a new record in articlePrice table>)

3 - else
update the corresponding ArticlePrice row.

My problem is in writing the sp, first it checks if the article exists, if it does then it creates a row in the article table and then one into the articlePrice table. My question is how do I get a hold on the "articleGUID"(pk - uniqueidentifier) that was created on the first table on insertion in order to create the row in the second table(fk)?

And is this possible with one stored procedure? If not does anyone know in Biztalk what other way to do this?

I am new to both Biztalk and sql, so I don't really know what I am doing? I would really appreciate any suggestions!

pp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 11:39:58

Refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=76453

Madhivanan

Failing to plan is Planning to fail

prettypixie
Starting Member

8 Posts

Posted - 2006-12-18 : 09:32:00

Well this is the sp that I made, which is not working. Can anyone look at it and tell me what I am doing wrong? And should I even be including the variables SupplierArticleGUID, SupplierArticlePriceGUID when they are going to be generated on row insertion?

CREATE PROCEDURE sp_UpdateArticle
(
--Article table
@SupplierArticleGUID uniqueidentifier,
@SupplierGUID uniqueidentifier,
@SupplierArticleNumber nvarchar(50),
@VatKeyValue int,

--ArticlePrice table
@SupplierArticlePriceGUID uniqueidentifier,
--@SupplierArticleGUID uniqueidentifier,
@ValidFrom datetime,
@ValidTo datetime,
@UnitOfMeasurement nvarchar(50),
@LotType uniqueidentifier,
@LotSize decimal(5,2),
@GrossWeight decimal(5,2),
@NetWeight decimal(5,2),
@UnitPrimeCost decimal(5,2),
@LotPrimeCost decimal(5,2),
@Currency nvarchar(50)
)
AS
SET @SupplierArticleGUID = newid()
IF NOT EXISTS(SELECT * FROM Article WHERE SupplierArticleNumber = @SupplierArticleNumber)
BEGIN
--SET @SupplierArticleGUID = newid()

SET @SupplierArticlePriceGUID = newid()
INSERT INTO Article
(
SupplierArticleGUID,
SupplierGUID,
SupplierArticleNumber,
VatKeyValue
)
VALUES
(
@SupplierArticleGUID,
@SupplierGUID,
@SupplierArticleNumber,
@VatKeyValue
)
--SET @SupplierArticleGUID = @@identity

insert into ArticlePrice
(
SupplierArticlePriceGUID,
SupplierArticleGUID,
ValidFrom,
ValidTo,
UnitOfMeasurement,
LotType,
LotSize,
GrossWeight,
NetWeight,
UnitPrimeCost,
LotPrimeCost,
Currency
)
VALUES
(
@SupplierArticlePriceGUID,
@SupplierArticleGUID,
@ValidFrom,
@ValidTo,
@UnitOfMeasurement,
@LotType,
@LotSize,
@GrossWeight,
@NetWeight,
@UnitPrimeCost,
@LotPrimeCost,
@Currency
)
END

ELSE

IF NOT EXISTS(SELECT * FROM ArticlePrice WHERE SupplierArticleGUID = @SupplierArticleGUID AND LotSize = @LotSize)
BEGIN
SET @SupplierArticlePriceGUID = newid()

insert into ArticlePrice
(
SupplierArticlePriceGUID,
SupplierArticleGUID,
ValidFrom,
ValidTo,
UnitOfMeasurement,
LotType,
LotSize,
GrossWeight,
NetWeight,
UnitPrimeCost,
LotPrimeCost,
Currency
)
VALUES
(
@SupplierArticlePriceGUID,
@SupplierArticleGUID,
@ValidFrom,
@ValidTo,
@UnitOfMeasurement,
@LotType,
@LotSize,
@GrossWeight,
@NetWeight,
@UnitPrimeCost,
@LotPrimeCost,
@Currency
)

END

--3rd PART - HOW TO UPDATE ArticlePrice table??

SET QUOTED_IDENTIFIER OFF

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources