Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 17th, 2004, 11:39 AM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Proc. problem - newbie

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
Reply With Quote
  #2 (permalink)  
Old November 17th, 2004, 12:28 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

You have an extra ) in the WHERE clause.
Reply With Quote
  #3 (permalink)  
Old November 17th, 2004, 01:48 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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

--

Joe (Microsoft MVP - XML)
Reply With Quote
  #4 (permalink)  
Old November 18th, 2004, 06:34 AM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old November 18th, 2004, 08:45 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
Reply With Quote
  #6 (permalink)  
Old November 22nd, 2004, 06:18 AM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling an insert stored proc from a select stored dzitam SQL Language 10 April 2nd, 2007 01:39 PM
problem with a stored proc trying to return blobs Phrozt SQL Server 2000 2 May 12th, 2006 10:36 AM
How to get value from stored proc busybee ASP.NET 1.0 and 1.1 Basics 4 April 2nd, 2006 01:06 AM
Problem to run delete and exec DTS in Stored Proc tyh79 SQL Server DTS 7 July 14th, 2004 11:27 AM
Stored Proc and Access mega Classic ASP Databases 2 June 14th, 2003 07:03 AM



All times are GMT -4. The time now is 04:48 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.