Post reply

The famous "Must declare the variable '@...'" again :(

  • October 31, 2008 at 7:37 am

    #200859

    I have googled for 2hrs now, found a lot of ppl with similar symptoms, but different probs - and none of them seemed to match my 'simple' case.

    Trying to alter following sp gives the error "Must declare the variable '@new_aendnr'."

    ALTER PROCEDURE "dbo"."PROC_STUELI_NeuAendnr" (@id_stueli Int, @lastus int,@new_aendnr Int OUTPUT)

    AS

    DECLARE @id_stammdsp int

    DECLARE @aendnr int

    SELECT @id_stammdsp = id_stammdsp FROM stueli WHERE stueli.id = @id_stueli

    SELECT @aendnr = max(aendnr) FROM stueli WHERE id_stammdsp = @id_stammdsp

    SET @new_aendnr = @aendnr + 1

    EXEC('INSERT INTO stueli (aendnr,id_stat_stueli,tvstueli,id_stammdsp,lgkalk,lastup,lastus)

    SELECT ' + @new_aendnr + ',id_stat_stueli,tvstueli,id_stammdsp,lgkalk,current_date,' + @lastus +

    ' FROM stueli WHERE (id_stammdsp=' + @id_stammdsp + ')AND aendnr=' + @aendnr)

    Why???

  • October 31, 2008 at 7:43 am

    #892329

    Can you fix the wrapping please?

    Does creating the proc give the error, or running it?

    I can create that without error on SQL 2008. Are you sure that's exactly what you're trying to run?

    What's the full message (complete with line number)?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 31, 2008 at 7:52 am

    #892337

    Thanks for looking at it so quickly 🙂 I have wrapped the last long statement.

    Oh no, this is embarrassing - it works! I had some "GO"-commands in there and had removed them before posting the code - and it seems I didn't execute it, When I just did, it compiled fine. Sorry for wasting forum-space and reading-time! :blush:

    Michael

  • October 31, 2008 at 8:05 am

    #892360

    😀

    GOs break batches and hence variables declared before will be out of scope afterwards.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 31, 2008 at 12:20 pm

    #892504

    The problem is in the header ALTER PROCEDURE "dbo"."PROC_STUELI_NeuAendnr" (@id_stueli Int, @lastus int,@new_aendnr Int OUTPUT)

    The @laststatus parameter does not have a data typpe...

    DAB

  • October 31, 2008 at 12:22 pm

    #892507

    My bad. I looked at that three times and did not see the INT. Never mind...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply