Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    11

    Unanswered: stored proc: query vals to local variables

    I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.

    Thats the dream.

    The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.

    Here is what I have as of this moment for my sp.

    Code:
    ALTER PROCEDURE usp_CalculatePunchcard 
    AS
    DECLARE @dtPP DateTime
    SET @dtPP = (SELECT thursday_in1
        FROM punchcards
        WHERE (punchcard_id = 1))
    /*
    Also tried....
    SELECT @dtPP=thursday_in1
        FROM punchcards
        WHERE (punchcard_id = 1)
    */
    
    PRINT @dtPP
    
    RETURN
    /* 
    for some reason i can't use GO ... even though every 
    document i've read on stored procedures has used GO 
    and none use RETURN
    */
    The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '

    Any help would be greatly appreciated as I am about to kick someone/something.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What happens when you EXECUTE the stored procedure?

    -PatP

  3. #3
    Join Date
    Jun 2004
    Posts
    11

    solved

    Ok....I figured out what I was doing wrong thanks to this article

    Code:
    ALTER PROCEDURE usp_CalculatePunchcard 
    AS
    DECLARE @day_in datetime
    SELECT @day_in = thursday_in1
    FROM punchcards WHERE (punchcard_id = 1)
    
    
    /* this is all I needed to do---> */ SELECT     punchin_time = @day_in
    The data was being retrieved properly and loaded into the variables I created in the sp but it wasn't being returned because I needed to use SELECT (duh!)

    Thanks for taking the time to reply
    Last edited by cmoney; 08-18-04 at 16:05.

Posting Permissions

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