Simple Stored Proc Problem

Results 1 to 2 of 2

Thread: Simple Stored Proc Problem

  1. #1
    Join Date
    Dec 1969

    Default Simple Stored Proc Problem

    I&#039;ve got two tables. One table (LMS_Users) has a unique UserID field and stores the user&#039;s DeptID. The other table (LMS_TestSessions) also contains the UserID and the DeptID, however the DeptID is TODAY&#039;s DeptID and will not change when the user changes departemnts, so I can&#039;t relate the two columns.<BR><BR>How can I update the blank DeptID fields in LMS_TestSessions with the current DeptID values in LMS_Users?<BR><BR>Here&#039;s what I&#039;ve got so far (doesn&#039;t work):<BR><BR>================================<BR> <BR>CREATE PROCEDURE [dbo].[LMS_TestSessionDeptIDUpdate] <BR>AS<BR>UPDATE LMS_TestSessions<BR>SET LMS_TestSessions.DeptID = LMS_Users.DeptID <BR>FROM LMS_Users INNER JOIN LMS_TestSessions<BR>ON (LMS_Users.UserID=LMS_TestSessions.UserID)<BR>WHER E LMS_TestSessions.DeptID &#060;&#062; LMS_Users.DeptID<BR>GO

  2. #2
    Join Date
    Dec 1969

    Default For starters...

    ...try doing it in Query Analyzer *without* using a Stored Proc! See what kind of error messages you get from just the UPDATE as you have coded it.<BR><BR>But for another thing, try telling us what "doesn&#039;t work" means! You get an error? Nothing happens? What?<BR><BR>ALSO... If the *current* value of LMS_TestSessions.DeptID happens to be NULL (you talk about "update the blank DeptID field..." -- does "blank" really mean "null"?), then when you do<BR> WHERE LMS_TestSessions.DeptID &#060;&#062; LMS_Users.DeptID<BR>it ain&#039;t a gonna work, because a NULL value is *NEVER* "&#060;&#062;" to a non-null value! (A NULL value is also never "=" to a non-null value...look in the SQL Server docs for how operators work with null values.)<BR><BR>So ...<BR><BR>*MAYBE* you need<BR> WHERE IsNull( LMS_TestSessions.DeptID, -1 ) &#060;&#062; LMS_Users.DeptID<BR>????<BR><BR>Again, that only matters if some of the DeptID values are NULL. And if "DeptID" is *not* a numeric field, then replace the -1 with some impossible string:<BR> WHERE IsNull( LMS_TestSessions.DeptID, &#039;&#039; ) &#060;&#062; LMS_Users.DeptID<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts