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

paul.hartland at fsmail.net said the following on 4/1/2004 12:14 AM:

>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 
>  
>
It's the syntax that you are using in your UPDATE statement that is 
causing you grief. (btw, I read your solution, which is fine too.. but I 
would do it this way)
I use inner joins all the time... you statement could be better written 
like so:

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




-- 
-Francisco