Help Creating a Stored Procedure in SQL (1 Viewer)

[vurna]

Hi,

I'm a regular user of MS Access. However I have a problem which I can't solve in MS Access and have to use SQL and I have limited knowledge of transact-sql.

I do monthly imports in Access of different figures but now I have to make a correction to old values.

I have a main table "prod_AP_Inv_POT" which is actually stored on an SQL server 2009. It's linked into my Access db and thats where I fill it with new data every month.

As I mentioned I need to make an adjustment to one of the fields in my SQL table, about 100k rows or so will be affected.
Savy as I am with MS access I've made an update query (by using design) but it doesn't want to cooperate with me. Basically it doesn't make the change, period. There's nothing wrong with the query it has more to do with the fact that the SQL table i'm doing changes in has 9milion rows.

Anyhow I need to create the exact same operation on the sql server instead but this is where my lack of skills in transact-sql fails me.

Below is my code from MS Access. I was wondering if someone can help me re-write it to a stored procedure in MS SQL?

UPDATE prod_AP_Inv_POT
 
INNER JOIN conv_ExpectedPaymentDate ON (prod_AP_Inv_POT.VendorCountryType = conv_ExpectedPaymentDate.GeoKey) 
AND (prod_AP_Inv_POT.DueDate = conv_ExpectedPaymentDate.DueDate) 
 
SET prod_AP_Inv_POT.ExpectedPaymentDate = [conv_ExpectedPaymentDate].[ExpectedPaymentDate]
 
WHERE (((prod_AP_Inv_POT.ExpectedPaymentDate) Is Null) 
AND ((prod_AP_Inv_POT.Ccode)="US10") AND ((prod_AP_Inv_POT.DT)="A0") 
AND ((prod_AP_Inv_POT.ClearingDate) Between #10/1/2011# And #11/30/2011#) 
AND ((prod_AP_Inv_POT.Overdue)="Not Paid in Time"));

I know I have to put the "new" code after "Begin" in the SQL procedure, that's kinda it.

Any help is appriciated!

 

[pbaldy]

Presuming you've used the "New stored procedure" option in Management Studio, where you see:

-- Insert statements for procedure here

you would put your SQL. T-SQL will want single quotes instead of double quotes around values, and also single quotes instead of # around date/time values. Start there and see how you get along.

Edit: Obviously you'd give it a proper name and such, and delete the parameter stuff since you aren't using any.

 

[vurna]

Good Evening,

Thanks for your input pbaldy! I did the changes you suggested. However I get stuck quite quickly. When I run my create procedure management studio barks at my use of Inner Join. I'm certain it's possible to use joins in sql because I've used them in view's before when creating these in design mode in sql server management studio.

I've looked at my old views and it seems that joins are put under something called "FROM". But as I get it i should not use "FROM" when making an update sql query.

I'll update if I come up with a solution here during the evening.

So far I've gotten to this:

CREATEPROCEDURE [AP_POT].[Update_ExpectedPaymentDate] 
-- Add the parameters for the stored procedure here
AS
BEGIN
UPDATE AP_POT.prod_AP_Inv_POT INNER JOIN Dim.ExpectedPaymentDate
ON (AP_POT.prod_AP_Inv_POT.VendorCountryType = Dim.ExpectedPaymentDate.GeoKey)
AND(AP_POT.prod_AP_Inv_POT.DueDate = Dim.ExpectedPaymentDate.DueDate)
SET AP_POT.prod_AP_Inv_POT.ExpectedPaymentDate = Dim.ExpectedPaymentDate.ExpectedPaymentDate
WHERE (((prod_AP_Inv_POT.ExpectedPaymentDate) Is Null)
AND((prod_AP_Inv_POT.ClearingDate) Between '11/1/2011# And #11/30/2011')
AND((prod_AP_Inv_POT.Overdue)='Not Paid in Time'));
END
GO

The error message I get is:
Msg 156, Level 15, State 1, Procedure Update_ExpectedPaymentDate, Line 12
Incorrect syntax near the keyword 'INNER'.
Msg 102, Level 15, State 1, Procedure Update_ExpectedPaymentDate, Line 18
Incorrect syntax near 'prod_AP_Inv_POT'.

 

[Kiwiman]

Howzit

Does this work?

UPDATE T1
SET T1.ExpectedPaymentDate = T2.ExpectedPaymentDate
FROM
	prod_AP_Inv_POT T1
INNER JOIN 
	conv_ExpectedPaymentDate T2
		ON T1.VendorCountryType = T2.GeoKey
		AND T1.DueDate = T2.DueDate
WHERE 
	T1.ExpectedPaymentDate is null
	AND T1.Ccode='US10'
	AND T1.DT='A0' 
	AND T1.ClearingDate Between '20111001' AND '20111130'
	AND T1..Overdue='Not Paid in Time';

 

[Fear Naught]

You will need to add a FROM statement. Without understanding your database structure I would suggest something like this:

CREATEPROCEDURE [AP_POT].[Update_ExpectedPaymentDate] 
-- Add the parameters for the stored procedure here
AS
BEGIN
UPDATE AP_POT.prod_AP_Inv_POT 
SET AP_POT.prod_AP_Inv_POT.ExpectedPaymentDate = Dim.ExpectedPaymentDate.ExpectedPaymentDate
FROM Dim INNER JOIN Dim.ExpectedPaymentDate
ON (AP_POT.prod_AP_Inv_POT.VendorCountryType = Dim.ExpectedPaymentDate.GeoKey)
AND(AP_POT.prod_AP_Inv_POT.DueDate = Dim.ExpectedPaymentDate.DueDate)
WHERE (((prod_AP_Inv_POT.ExpectedPaymentDate) Is Null)
AND((prod_AP_Inv_POT.ClearingDate) Between '11/1/2011# And #11/30/2011') AND((prod_AP_Inv_POT.Overdue)='Not Paid in Time'));
END
GO

Hope this helps.

 

[vurna]

Thanks guys for your input, really appriciated. Trying both here atm.

Feels like it's getting closer.
I started off with your input Fear Naught. And I inputed this into the procedure:

CREATE PROCEDURE [AP_POT].[Update_ExpectedPaymentDate] 
-- Add the parameters for the stored procedure here
AS
BEGIN
UPDATE AP_POT.prod_AP_Inv_POT
SET AP_POT.prod_AP_Inv_POT.ExpectedPaymentDate = Dim.ExpectedPaymentDate.ExpectedPaymentDate
FROM Dim INNER JOIN Dim.ExpectedPaymentDate
ON (AP_POT.prod_AP_Inv_POT.VendorCountryType = Dim.ExpectedPaymentDate.GeoKey) 
AND (AP_POT.prod_AP_Inv_POT.DueDate = Dim.ExpectedPaymentDate.DueDate) 
WHERE (((prod_AP_Inv_POT.ExpectedPaymentDate) Is Null) 
AND ((ClearingDate BETWEEN CONVERT(DATETIME, '2011-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-11-30 00:00:00', 102) 
AND ((prod_AP_Inv_POT.Overdue) = 'Not Paid in Time' ));
END
GO

I didn't know why the "Dim" before Inner Join was there but when I tested to remove it I got an error. So I'm keeping it. Then I got stuck at the date row but I had an old query someone helped me a long time ago with so I copied the convert thingy into here. Then I got past that one.
Now it's just stuck on the last row of code saying:
Msg 102, Level 15, State 1, Procedure Update_ExpectedPaymentDate, Line 19
Incorrect syntax near ';'.
I tried removing the ";" but then it complains about "END" instead. I also tried randomly inserting some ")" into the text but that didn't end well either.


Kiwiman, in your example should I take away T1 and T2 and put my table name there?

 

[Fear Naught]

The CONVERT function is a good point and I missed that in my earlier post!!

I have made some changes to the bracketing used and have removed some in the code below.

I'm not sure that you really need the BEGIN END block so have removed them.

See how this goes:

CREATE PROCEDURE [AP_POT].[Update_ExpectedPaymentDate]
AS 
UPDATE AP_POT.prod_AP_Inv_POT 
SET AP_POT.prod_AP_Inv_POT.ExpectedPaymentDate = Dim.ExpectedPaymentDate.ExpectedPaymentDate
FROM Dim INNER JOIN Dim.ExpectedPaymentDate ON AP_POT.prod_AP_Inv_POT.VendorCountryType = Dim.ExpectedPaymentDate.GeoKey
AND AP_POT.prod_AP_Inv_POT.DueDate = Dim.ExpectedPaymentDate.DueDate 
WHERE prod_AP_Inv_POT.ExpectedPaymentDate Is Null 
AND (ClearingDate BETWEEN CONVERT(DATETIME, '2011-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-11-30 00:00:00', 102)) 
AND prod_AP_Inv_POT.Overdue) = 'Not Paid in Time' ;

 

[Kiwiman]

Howzit

The T1 and T2 are the aliases for your 2 tables. Just saves you having to fully reference the full table name in other parts of the query. So they should be left as it is.

FROM
	prod_AP_Inv_POT [B]T1[/B]
INNER JOIN 
	conv_ExpectedPaymentDate [B]T2[/B]

I show for example, T1.ExpectedPaymentDate which is the same as your prod_AP_Inv_POT.ExpectedPaymentDate.

So wherever T1 is mentioned it is getting a field from the prod_AP_INV_POT table etc.

 

[vurna]

Hey guys,

It's finally working for me now! The procudure was created successfully and I have also applied the changes to the actual data by executing the procedure.
I'm saving these for future reference if I ever need to do something like this again.

Big thanks to both of you!!