Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: create procedure error

    hi. i'm trying to create a stored procedure but it keeps messing up and i have absolutely no clue why. here is what i have:

    Code:
    CREATE PROCEDURE sp_OfficeReportStats AS
    
    
    ------------------------------------------------------------
    --NEW CASE
    ------------------------------------------------------------
    
    --NC2
    CREATE TABLE TempWorkDB
    (
    ProsAtty SMALLINT, Stat INT
    )
    GO
    
    INSERT INTO TempWorkDB (ProsAtty, Stat)
    
    SELECT DefendantCase.ProsAtty, COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey = DefendantEventPros.VBKey WHERE DefendantEventPros.EventID=2 AND DefendantEventPros.EventDate BETWEEN DATEADD(MONTH,-2,GETDATE()) AND GETDATE() GROUP BY DefendantCase.ProsAtty
    GO
    
    UPDATE OfficeReport SET NC2=TempWorkDB.Stat FROM TempWorkDB WHERE TempWorkDB.Prosatty=OfficeReport.ProsAtty
    GO
    
    UPDATE OfficeReport SET NC2=0 WHERE NC2 IS NULL
    GO
    
    DROP TABLE TempWorkDB
    GO
    this code works in query analyzer just fine but it says i have an error at TempWorkDB. I do not have a TempWorkDB in my database currently. Waht am I doing wrong? thanks for you help!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You cannot have "Go"s in your sproc. The first GO marks the end of the create proc. GO is not really T-SQL but informs the client tool that it is to submit the batch.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by bla4free
    hi. i'm trying to create a stored procedure but it keeps messing up and i have absolutely no clue why. here is what i have:
    Well, first off, it isn't messing up.

    Second you don't post the error, but I suspect that since the create isn't committed, that the table isn't seen yet. Could be wrong here, BUT

    Third, if you want to use a table you should use a temp table (CREATE TABLE #mytemp...)

    Fourth, you don't need a table at all, you need to do

    Code:
        UPDATE  O 
           SET NC2=TempWorkDB.Stat 
          FROM OfficeReport		O
    INNER JOIN DefendantCase 	D
     LEFT JOIN DefendantEventPros	E 
            ON D.VBKey = E.VBKey 
         WHERE E.EventID=2 
           AND E.EventDate BETWEEN DATEADD(MONTH,-2,GETDATE()) 
           AND GETDATE() 
    GROUP BY DefendantCase.ProsAtty
           AND D.Prosatty=O.ProsAtty
    GO
    Damn...Waht the hell is this?

    AND GETDATE()


    You have a malformed UPDATE in the first place
    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.

  4. #4
    Join Date
    Jan 2005
    Posts
    165
    @pootle flump: that fixed it! thanks for the help!
    Last edited by bla4free; 02-23-07 at 11:18.

  5. #5
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by Brett Kaiser

    Damn...Waht the hell is this?

    AND GETDATE()


    You have a malformed UPDATE in the first place
    what do you mean by AND GETDATE()? should i be using something else?

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Brett Kaiser
    Damn...Waht the hell is this?

    AND GETDATE()
    It's part of the 'Between', it shouldn't be aligned like that.

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    how should it be aligned then? thanks!

  8. #8
    Join Date
    Nov 2002
    Posts
    272
    I would do it like this:
    Code:
           AND E.EventDate BETWEEN DATEADD(MONTH,-2,GETDATE()) 
                               AND GETDATE()

Posting Permissions

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