Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    30

    Question Unanswered: sql output parameters

    I am new to SQL and trying to work out how to do this

    i have a table called projectAllocationLog with 4 fields, there can be many users of any particular project, ie
    ID,projectID, User, Date
    4 5000, 6, 10/02/2007
    3 5000, 12, 08/07/2005
    2 5004, 13, 08/05/2003
    1 5000, 14, 04/05/2001

    I want to write a stored proceedure that I can pass in the projectID, and return the most current user, this will always be the user with the highest ID for the project

    ie for project 5000 it will be user 6

    I was thinking along the lines of for each project select the top 1 from the table. where I can pass in the projectID, and pass out the ID field of the table. i am just not sure how to start this.

    Any help would be appreciated.

  2. #2
    Join Date
    Oct 2007
    Posts
    30
    Further to my earlier email, i think i have it sorted. This is what I have come up with, any comments from more experience programmers are welcome.

    CREATE PROCEDURE dbo.sp_test
    (
    @iProjectid INT,
    @iuserID INT OUTPUT
    )

    AS

    SELECT TOP 1 P.projectallocationlogid, P.projectid, P.newuserid, P.createdate
    into #temp
    FROM ProjectAllocationLog P
    WHERE P.projectid=@iProjectid
    ORDER BY createdate DESC

    SELECT @iuserID = newuserid
    FROM #TEMP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't think you need a temp table

    and your first post did say the user with the highest id, which for project 5000 would be user 16
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    She said any more expereinced programmers

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are quite correct, brett

    the user with the highest id for project 5000 is actually user 14
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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