Stored Proc. problem

joeb · November 17th, 2004, 11:39 AM

Hi,
I've just started writing some stored procedures to optimize some things on my database, but it seems I got some syntax wrong. Would you mind having a look on the code:

Code:

CREATE PROCEDURE  cp_procedure
AS

SELECT  A.a, A.b, A.c, A.d, B.a
FROM A LEFT JOIN B ON (A.b = B.a)
SET A.a = False
SET A.b = 9999
SET A.c = [Amount] 
WHERE (A.d)='F')
AND (B.a) IS NULL

GO

planoie · November 17th, 2004, 12:28 PM

You have an extra ) in the WHERE clause.

joefawcett · November 17th, 2004, 01:48 PM

Your select and your update are separate operations, you need to specify the table used in the update part.

--

Joe (Microsoft MVP - XML)

joeb · November 18th, 2004, 06:34 AM

Thanks for the input. The extra ) was an mistype :O)

I introduced the UPDATE but still get an error in line 8 (incorrect syntax near '.'). Am I'm missing something obvious here?

Code:

CREATE PROCEDURE  cp_procedure
AS

SELECT  A.a, A.b, A.c, A.d, B.a
FROM A LEFT JOIN B ON (A.b = B.a)
UPDATE A
SET A.a = False
SET A.b = 9999 -- What is wrong in this line 8?
SET A.c = [Amount] 
WHERE (A.d)='F'
AND (B.a) IS NULL

GO

joefawcett · November 18th, 2004, 08:45 AM

If this is SQL Server then False is not valid, you normally use 1 and 0 in a bit field for true/false. You also need commas separating the individual SET parts:

Code:

UPDATE A
SET A.a = 0,
SET A.b = 9999,
SET A.c = [Amount] 
WHERE (A.d)='F'
AND (B.a) IS NULL

You also have not included B in the update line. Your original SELECT is totally separate from your update.

--

Joe (Microsoft MVP - XML)

joeb · November 22nd, 2004, 06:18 AM

Thanks for helping me put here...
I've tried to rewrite it:

Code:

UPDATE A
SET  A.a = 0, A.b = 9999,  A.c = A.d
WHERE(
      SELECT  A.a, A.b, A.c, A.d, B.a
      FROM A
      LEFT JOIN B ON
      A.b = B.a
      WHERE (A.c = 'F')  AND (B.a IS NULL) 
     )

Still no go... damn.