[dba-SQLServer] Stored Procedure Help (SQL Server & VB6)

Hi paul,
 
nice to see you here also.
 
Personally I do NOT use the INNER JOIN stuff. I just use plain SQL
SELECT a.x, b.y
FROM table1 a, table2 b
WHERE a.a = b.a
AND ....
That would result in:
UPDATE tblPersonnelJobCategories 
   SET tblPersonnelJobCategories.Selected = True 
 WHERE tblPersonnelJobCategories.UserID= @UserId
   AND tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID
   AND tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo

 
A tip for you're query: does it run when you hardcode the UserID?
 
Did you try to run:
SELECT tblPersonnelJobCategories.UserID,
       tblPersonnelJobCategories.Selected 
  FROM tblPersonnelJobCategories, 
       tblPersonnelCategoriesSelected    
 WHERE tblPersonnelJobCategories.UserID= @UserId
   AND tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID
   AND tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo

Success.
 
Sander


paul.hartland at fsmail.net wrote:
To all,

I have a Visual Basic 6 front-end together with a SQL Server 7.0 back-end, and have tried writing a stored procedure to do the following:

I pass a UserID into the SP, then I want the SP to UPDATE tblPersonnelJobCategories to TRUE where tblPersonnelJobCategories.UserID is equal to the UserID that I passed to the SP but I only want the UPDATE to take place where the PayrollNo & CategoryID in tblPersonnelJobCategories matches the PayrollNo & CategoryID in tblPersonnelCategoriesSelected

I tried using the follwing SQL statement in my SP but it doesn’t like the Inner Join:

UPDATE tblPersonnelJobCategories INNER JOIN tblPersonnelCategoriesSelected ON (tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID) AND (tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo) SET tblPersonnelJobCategories.Selected = True WHERE tblPersonnelJobCategories.UserID= @UserId

Any help would be greatly appreciated, as I’m fairly new to SP’s and thought an INNER JOIN would be ok.

Thanks in advance for any help
Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
www.freeserve.com/anytime
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam