Need help with stored proc - 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 2008 Forums

Transact-SQL (2008)

Need help with stored proc

Author

Topic

JohnDW
Starting Member

45 Posts

Posted - 2012-09-06 : 16:39:15

Hello,

I'm trying to create a stored procedure in sql server 2008.

But I need help.

First of all I want to fill a temporary table
with an sql query.
[CODE]
Declare @Temp Table (Klantnummer int, Naamvoornaam nvarchar, MaxVanOrderdatum date, Tal int, txtKortingBdr decimal, Gemeente nvarchar)

-- this is the query to fill @Temp
select Klant.Klantnummer, Klant.Naamvoornaam, Max(Klantenkaart.Orderdatum) AS MaxVanOrderdatum, Count(Klant.txtKortingBdr) AS Tal, Klant.txtKortingBdr, Gemeente.Gemeente
FROM Gemeente INNER JOIN (Klant INNER JOIN Klantenkaart ON Klant.Klantnummer = Klantenkaart.Klantnummer) ON Gemeente.GemeenteId = Klant.GemeenteId
GROUP BY Klant.Klantnummer, Klant.Naamvoornaam, Klant.txtKortingBdr, Gemeente.Gemeente
ORDER BY Klant.Klantnummer, Max(Klantenkaart.Orderdatum)
[\CODE]

Then I want to loop through the temp table
and for each record use an if statement . If the if statement is positive then I need a query that updates and delete in the tables of the db.
[CODE]
WHILE EXISTS (select Klantnummer, MaxVanOrderdatum from @Temp)

BEGIN
Select Top 1 @Klantnummer = Klantnummer, @MaxVanOrderdatum = MaxVanOrderdatum from @Temp
If MaxVanOrderdatum < DATEADD("yyyy", -2, Date) Then
-- The statement above with the DATE value doesn't work.
Insert into Delkk (Orderdatum, txtKortingBdr, Klantnummer, Tal) values
('" & MaxVanOrderdatum & "' , '" & txtKortingBedr & "' , '" & Klantnummer & "' , '" & Tal & "')"
Delete from Klantenkaart where Klantnummer = @Klantnummer

--I delete the record from the Temp Table to go to the next record.
Delete from @Temp where klantnummer = @klantnummer

END
[\CODE]
That's what I want.
Is there someone who can guide me to make this thing work as a
stored proc?

Txs,
John

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-06 : 17:54:03

This can all be done in one query, but it's hard to test without a database. Can you post DDL, DML and expected output per the links below:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

JohnDW
Starting Member

45 Posts

Posted - 2012-09-06 : 18:05:17

Txs Lamprey,
But maybe there is somewhere an example that works with a loop in a temporary table.
It would be fine if I can find that, because I've looked for it without finding...

John

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-06 : 18:05:44

You might try this, but I cannot test it. If it doesn't work, please see the links above to post DDL, DML and expected output.

DELETE 
    A
FROM
    Klantenkaart AS A
INNER JOIN
(
    INSERT
        Delkk
        (
            Orderdatum, 
            txtKortingBdr, 
            Klantnummer, 
            Tal
        )
    OUTPUT
        (
            Klantnummer
            --inserted.Klantnummer -- might need inserted here??
        )
    select 
        Klant.Klantnummer, 
        Klant.Naamvoornaam, 
        Max(Klantenkaart.Orderdatum) AS MaxVanOrderdatum, 
        Count(Klant.txtKortingBdr) AS Tal 
        --Klant.txtKortingBdr, 
        --Gemeente.Gemeente
    FROM 
        Gemeente 
    INNER JOIN 
        Klant 
        ON Gemeente.GemeenteId = Klant.GemeenteId
    INNER JOIN 
        Klantenkaart 
        ON Klant.Klantnummer = Klantenkaart.Klantnummer
    GROUP BY 
        Klant.Klantnummer, 
        Klant.Naamvoornaam, 
        Klant.txtKortingBdr, 
        Gemeente.Gemeente
    HAVING 
        Max(Klantenkaart.Orderdatum) < DATEADD(YEAR, -2, SYSDATETIME()) -- ??
) AS B
ON A.Klantenkaart = B.Klantenkaart

JohnDW
Starting Member

45 Posts

Posted - 2012-09-07 : 00:57:39

Txs,

I'll try it out and let something now.

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources