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

I personnally stay away from where joins. I've seen them all to often 
yield different results.. perhaps the developer didn't know how to use 
them, but I've always stuck to INNER/LEFT/RIGHT Joins... and 
specifically because you will tend to yield a better query plan. Have 
you run the statistics to see how many table scans you get from joining 
in the where clause?



S D said the following on 4/1/2004 4:37 AM:

>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.
>  
>


-- 
-Francisco