Store Procedure error

[gmatriix]

Hello All,

I am a little new to writing stored procedures...so forgive me. However, I am getting this message

Msg 156, Level 15, State 1, Procedure SP_GetandUpdateQuoteRate, Line 15
Incorrect syntax near the keyword 'LEFT'.
Msg 102, Level 15, State 1, Procedure SP_GetandUpdateQuoteRate, Line 16
Incorrect syntax near 'Routing'.

On this coded

CREATE PROCEDURE dbo.SP_GetandUpdateQuoteRate
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE Routing LEFT JOIN Department ON Routing.Department = Department.Department SET Routing.QuoteRate = department!rate
WHERE (((Routing.QuoteRate)=0 Or (Routing.QuoteRate) Is Null));
END
GO

I tried it again with this

UPDATE    Routing
SET              QuoteRate = Department.Rate
FROM         Routing LEFT OUTER JOIN
                      Department ON Routing.Departmentid = Department.Department
WHERE     (Routing.QuoteRate IS NULL) OR
                      (Routing.QuoteRate = 0)

I am still getting an error
Msg 102, Level 15, State 1, Procedure SP_UpdateRoutingRates, Line 20
Incorrect syntax near ')'.


Please Help!!

Any ideas?

 

[mdlueck]

UPDATE and LEFT JOIN seem mutually exclusive in my mind. LEFT JOIN is used with SELECT. When I issue an UPDATE, I only ever UPDATE one table at a time, no joining.

 

[gmatriix]

Thanks for replying,

When I took out the "Begin" in the 1st code and kept what was in the second code. it worked. The visual editor helped alot since I am very use to writing Access queries and vba code.

Thanks for your help

 

[mdlueck]

When I took out the "Begin" in the 1st code and kept what was in the second code. it worked.

SSMS template of Stored Procedures has BEGIN / END wrapping the SQL worker code of the procedure. I think you best start with an SSMS template and see how to tidy up your code.

Yes, looking at your code again I can see how UPDATE and LEFT JOIN could be used together.

An example SP from my collection:

:setvar SPNAME clsObjProjectsTbl_LocateProjectByID
USE [Fandango]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Check if the STORED PROCEDURE exists already, DROP if found
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [type_desc] = 'SQL_STORED_PROCEDURE' AND [name] = '$(SPNAME)')
  DROP PROCEDURE [dbo].[$(SPNAME)]
GO
CREATE PROCEDURE [dbo].[$(SPNAME)] (
  -- Add the parameters for the stored procedure here
  @id AS smallint
  )
AS
[B][COLOR=Blue]BEGIN[/COLOR][/B]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRAN

SELECT [p].[id],
       [p].[authid],
       [a].[username] AS [authusername],
       [p].[logtimestamp],
       [p].[title],
       [p].[budget],
       [p].[rptactiveflg],
       [p].[rpttitle]
FROM [dbo].[projects] AS [p]
LEFT JOIN [dbo].[auth] AS [a] ON [p].[authid] = [a].[id]
WHERE [p].[id] = @id;

COMMIT TRAN

SET NOCOUNT OFF
[COLOR=Blue][B]END[/B][/COLOR]

 

[gmatriix]

Your right....I will start with that.... I'm still learning...

By the way....do you know of a easy way to pass parameters? (for my reference later)

Thanks again!

 

[mdlueck]

do you know of a easy way to pass parameters?

Pass them how? From VBA to the SP? That I do two ways depending on if I need the SP to return to VBA variables or to populate a FE temp table. Here are examples of both:

Using VBA ADO objects to execute a Stored Procedure (INSERT)
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605

An overview of how I use Access in the Client/Server environment:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

Access-centric Samples, only need to change the connection object to utilize for SQL BE DB's

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

ADO.Connection object to SQL BE DB
http://www.access-programmers.co.uk/forums/showthread.php?t=231923#post1184259

 

[gmatriix]

Thank You!

These are very helpful! I have a Access db FE with SQL BE. The problem (as you may well know) is that running queries from Access on a lot of data kills performance.

So I am wanting to have SQL do the work and only display results in Access. So I wanted to pass the parameter from Access to SQL so that SQL can run the query rather Access struggling with it.

In essence is what im trying to do....

 

[mdlueck]

You have the correct idea, gmatriix!!!

 

[SQL_Hell]

Mdlueck, in your example stored procedure why is there a transaction around a select statement? Is this is a mistake?

 

[mdlueck]

That was in the template SSMS provided. I added the bit to remove the existing SP / variable the name of the SP.

 

[Galaxiom]

Also note that the prefix SP_ should not be used for user-defined stored procedures.

SP_ means "System Procedure" and there is a risk that you could hit on the name of an existing system procedure.