Stored procedures and moving through a table with

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)

Stored procedures and moving through a table with

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-07 : 08:19:03

Sascha writes "Hello,

I hope that you can help me getting this stored procedure to work. What I want to do: select a row from a table and use the returned values to call a stored procedure with parameters.
But when I use the "SQL Query Analyzer" then nothing happens and I haven't worked with "fetch" and "cursor".

THis is my first stored procedure:
--------------------------

CREATE PROCEDURE ca_Angebotsimport AS
BEGIN

declare @NPAFNR dec(5)
declare @NPPOSN dec(5)

declare cursor_ang INSENSITIVE CURSOR FOR
select NPAFNR,NPPOSN FROM ANFPPU

OPEN cursor_ang

FETCH FROM cursor_ang INTO @NPAFNR,@NPPOSN
WHILE @@Fetch_Status = 0
BEGIN

exec imp_Angebotsimport @NPAFNR,@NPPOSN
FETCH FROM cursor_ang INTO @NPAFNR,@NPPOSN

END

close cursor_ang
deallocate cursor_ang

END

GO

------------
and this is the second part:
------------

CREATE PROCEDURE imp_Angebotsimport (@ANNummer dec(5) OUTPUT, @ANPos dec(5) OUTPUT) AS

BEGIN

CREATE TABLE #tAngebote (
Nummer dec(5) IDENTITY(1,2),
Pos dec(5) IDENTITY(2,2),
Text2 char(45),
wert dec(9),
Artikel char(30),
menge dec(9),
kunde char(12)
)

DECLARE cursor_ang INSENSITIVE CURSOR FOR
select ANNummer,ANPos FROM Angebote WHERE ANNummer = @ANNummer AND ANPos = @ANPos

open cursor_ang

IF NOT exists (select ANNummer,ANPos FROM Angebote WHERE ANNummer = @ANNummer AND ANPos = @ANPos)

INSERT INTO Angebote
(
ANNummer,
ANPos,
ANText,
ANWert,
ARArtikel,
ANMenge,
ANKunde
)
SELECT NPAFNR,NPPOSN, nk.NKAFTX, NPPRMA, NPARTN, NPPOMG, NPADRN
FROM ANFPPU INNER JOIN ANFKPU AS nk ON nk.NKAFNR = NPAFNR
WHERE NPFIRM = '001' AND NPSTA1 <> 'L' AND NPAFNR = @ANNummer AND NPPOSN = @ANPos

ELSE

INSERT INTO #tAngebote
SELECT * FROM ANFPPU INNER JOIN ANFKPU AS nk ON NPAFNR = nk.NKAFNR
WHERE NPFIRM = '001' AND nk.NKFIRM = '001' AND NPSTA1 <> 'L' AND nk.NKSTA1 <> 'L'
AND NPAFNR = @ANNummer AND NPPOSN = @ANPos

UPDATE Angebote SET
ANNummer = (SELECT Nummer FROM #tAngebote),
ANPos = (SELECT Pos FROM #tAngebote),
ANText = (SELECT Text2 FROM #tAngebote),
ANWert = (SELECT Wert FROM #tAngebote),
ARArtikel = (SELECT Artikel FROM #tAngebote),
ANMenge =(SELECT Menge FROM #tAngebote),
ANKunde = (SELECT Kunde FROM #tAngebote)
WHERE ANNummer = @ANNummer AND ANPos = @ANPos

END
GO

----------------
how can I use some kind of output variables in my code ? I tried some methods but didn't get anything ... is there also some kind of debugger in SQL Query Analyzer ?

Thanks for any help !!!

Sascha (from Germany)"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-07 : 09:46:09

Since you didn't post the DDL for ANFPPU and ANFKPU, there may be some type-o's in here ....

Look, there is no reason to use a cursor for this operation. The row by row iteration will be much slower than using set based methods.

It's you lucky day though. Fridays is cursor slaying days for me, so you get a freebie . . .


create procedure ca_Angebotsimport
as

--insert the ones that don't exists
insert into Angebote (
    ANNummer, 
    ANPos, 
    ANText, 
    ANWert, 
    ARArtikel, 
    ANMenge, 
    ANKunde ) 
select
    np.NPAFNR,
    np.NPPOSN,
    nk.NKAFTX,
    np.NPPRMA,
    np.NPARTN,
    np.NPPOMG,
    np.NPADRN 
from
    ANFPPU np
    inner join ANFKPU nk 
        on nk.NKAFNR = np.NPAFNR 
where
    np.NPFIRM = '001' AND
    np.NPSTA1 <> 'L' AND
    not exists (
        select 1
        from
            Angebot
        where
            np.NPAFNR = ANNummer and
            np.NPPOSN = ANPos)

--update the ones that do exists
update
    Angebote
set
    ANNummer    = np.NPAFNR,
    ANPos       = np.NPPOSN,
    ANText      = nk.NKAFTX,
    ANWert      = np.NPPRMA,
    ARArtikel   = np.NPARTN,
    ANMenge     = np.NPPOMG,
    ANKunde     = np.NPADRN
from
    Angebote a
    inner join ANFPPU np
        on (a.ANNummer = np.NPAFNR and
            a.ANPos = np.NPPOSN)
    inner join ANFKPU nk
        on nk.nkAFNR = NP.NPAFNR
where
    np.NPFRIM = '001' and
    nk.NKFIRM = '001' and
    np.NPSTA1 <> 'L' and
    nk.NKSTA1 <> 'L'
go

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources