paul.hartland at fsmail.net
paul.hartland at fsmail.net
Thu Apr 1 07:27:22 CST 2004
Thanks for the response, I ended up cheating. I created a view to select the records I needed to update then use an SP to update them..... Paul Message date : Apr 01 2004, 01:38 PM >From : "S D" To : dba-sqlserver at databaseadvisors.com Copy to : Subject : Re: [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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month www.freeserve.com/anytime