Help for stored procedure...

  • Thread starter Thread starter piero
  • Start date Start date
P

piero

Hi
I'm very newbie about SqlServer... so please be patient...
And please be patient also with my bad english.. I hope my question clear.

Tables:
TEMPIMPORTAZIONEDIST1
(contains data get from another application which include purchase items:
customer data, item code, item desc., ecc.)
ANAUTENTI
(contains all customers' data, name, address, etc)
ORDINIUTENTI
(contains item purchased from any customer)
I have a relationship between ANAUTENTI and ORDINIUTENTI Using "piva" fiels
which is the tax ID of customer.
ANAARTICOLI
(contains a complete items collection... I omit the reason which makes me do
this kind of database, is complicated to explain.. :-)


Goal:
Create a single Stored Procedure that :
1) Gets from TEMPIMPORTAZIONEDIST1 the piva field grouped and if in
ANAUTENTI this particular "piva" not exist, it add.
(but "piva" is unique field, so if value is present, I have to block error,
simple not add.)
2) Get any row in TEMPIMPORTAZIONEDIST1 (single order) without grouping, and
add it to ORDINIUTENTI ...
there are a relationship about "piva", so I will have One single customer
with more orders
note that here I check for item if exist in ANAARTICOLI and I get some other
value in ANAARTICOLI instead ORDINIUTENTI... there are a kind of table of
relation.

I would call stored procedure using asp and I would get back some value:
1) Know if stored procedure get error
2) Know how many row processed in first round
3) Know hom many row processed in second round

Here the (bad!) stored procedure I've try to build:
============================
ALTER PROCEDURE ImportaDIST1
AS
/* SET NOCOUNT ON */
DECLARE @ErrorSave INT , @righe1 INT , @righe2 INT
SET @ErrorSave = 0
SET @righe1 = 0
SET @righe2 = 0
INSERT INTO dbo.ANAUTENTI
(piva)
SELECT piva
FROM dbo.TEMPIMPORTAZIONEDIST1
GROUP BY piva
Set @righe1 = @@ROWCOUNT
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR

INSERT INTO dbo.ORDINIUTENTI
(pivautente, qnt, codiceartdistributore,
puntiarticolo, codiceartimation, dataacquisto, codicedistributore,
prezzolistino, unitaconfezionamento)
SELECT dbo.TEMPIMPORTAZIONEDIST1.piva, dbo.TEMPIMPORTAZIONEDIST1.qnt,
dbo.TEMPIMPORTAZIONEDIST1.codicearticolo, dbo.ANAARTICOLI.punti,
dbo.ANAARTICOLI.codartimation,
dbo.TEMPIMPORTAZIONEDIST1.datafattura, 'dist1' AS codicedistributore,
dbo.ANAARTICOLI.prezzolistinoimation,
dbo.ANAARTICOLI.pzconfezioneimation
FROM dbo.TEMPIMPORTAZIONEDIST1 INNER JOIN
dbo.ANAARTICOLI ON
dbo.TEMPIMPORTAZIONEDIST1.codicearticolo = dbo.ANAARTICOLI.codartdist1
Set @righe2 = @@ROWCOUNT
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR

RETURN
=====================================
Here the asp code I use to call procedure and get back value.

===================================
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_ordini_STRING
Command1.CommandText = "dbo.importadist1"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Execute()
response.Write(Command1.Parameters.Item("@RETURN_VALUE").Value)
===================================

Obviously I have make more than one thing wrong...

Someone can help me ?

Thanks in advance.

Piero
(italy)
 
look into Fetch.

piero said:
Hi
I'm very newbie about SqlServer... so please be patient...
And please be patient also with my bad english.. I hope my question clear.

Tables:
TEMPIMPORTAZIONEDIST1
(contains data get from another application which include purchase items:
customer data, item code, item desc., ecc.)
ANAUTENTI
(contains all customers' data, name, address, etc)
ORDINIUTENTI
(contains item purchased from any customer)
I have a relationship between ANAUTENTI and ORDINIUTENTI Using "piva" fiels
which is the tax ID of customer.
ANAARTICOLI
(contains a complete items collection... I omit the reason which makes me do
this kind of database, is complicated to explain.. :-)


Goal:
Create a single Stored Procedure that :
1) Gets from TEMPIMPORTAZIONEDIST1 the piva field grouped and if in
ANAUTENTI this particular "piva" not exist, it add.
(but "piva" is unique field, so if value is present, I have to block error,
simple not add.)
2) Get any row in TEMPIMPORTAZIONEDIST1 (single order) without grouping, and
add it to ORDINIUTENTI ...
there are a relationship about "piva", so I will have One single customer
with more orders
note that here I check for item if exist in ANAARTICOLI and I get some other
value in ANAARTICOLI instead ORDINIUTENTI... there are a kind of table of
relation.

I would call stored procedure using asp and I would get back some value:
1) Know if stored procedure get error
2) Know how many row processed in first round
3) Know hom many row processed in second round

Here the (bad!) stored procedure I've try to build:
============================
ALTER PROCEDURE ImportaDIST1
AS
/* SET NOCOUNT ON */
DECLARE @ErrorSave INT , @righe1 INT , @righe2 INT
SET @ErrorSave = 0
SET @righe1 = 0
SET @righe2 = 0
INSERT INTO dbo.ANAUTENTI
(piva)
SELECT piva
FROM dbo.TEMPIMPORTAZIONEDIST1
GROUP BY piva
Set @righe1 = @@ROWCOUNT
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR

INSERT INTO dbo.ORDINIUTENTI
(pivautente, qnt, codiceartdistributore,
puntiarticolo, codiceartimation, dataacquisto, codicedistributore,
prezzolistino, unitaconfezionamento)
SELECT dbo.TEMPIMPORTAZIONEDIST1.piva, dbo.TEMPIMPORTAZIONEDIST1.qnt,
dbo.TEMPIMPORTAZIONEDIST1.codicearticolo, dbo.ANAARTICOLI.punti,
dbo.ANAARTICOLI.codartimation,
dbo.TEMPIMPORTAZIONEDIST1.datafattura, 'dist1' AS codicedistributore,
dbo.ANAARTICOLI.prezzolistinoimation,
dbo.ANAARTICOLI.pzconfezioneimation
FROM dbo.TEMPIMPORTAZIONEDIST1 INNER JOIN
dbo.ANAARTICOLI ON
dbo.TEMPIMPORTAZIONEDIST1.codicearticolo = dbo.ANAARTICOLI.codartdist1
Set @righe2 = @@ROWCOUNT
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR

RETURN
=====================================
Here the asp code I use to call procedure and get back value.

===================================
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_ordini_STRING
Command1.CommandText = "dbo.importadist1"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Execute()
response.Write(Command1.Parameters.Item("@RETURN_VALUE").Value)
===================================

Obviously I have make more than one thing wrong...

Someone can help me ?

Thanks in advance.

Piero
(italy)
Click to expand...
 
Back
Top