Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Angry Unanswered: Stored Procedure to Insert with previous Seeded ID

    Thanks in advance for reading an and contemplating the following situation. When I run the follwoing code in Query Analyzer I get the error:

    Procedure 'sp_insert_agency' expects parameter '@fkAgency', which was not supplied.

    What am I doing wrong?

    <!-- Begin Code -->

    USE AA
    GO

    IF EXISTS (SELECT name
    FROM sysobjects
    WHERE name = 'sp_insert_agency'
    AND type = 'P')
    DROP PROCEDURE [dbo].[sp_insert_agency]
    GO

    CREATE PROCEDURE [dbo].[sp_insert_agency]

    /* Table One */
    @AgencyName [varchar](64),
    /* Table Two */
    @fkAgency [int],
    @strFacName [varchar](75),
    @strFacStreet [varchar](50),
    @strFacCity [varchar](50),
    @strFacState [varchar](50),
    @strFacZip [varchar](50),
    @strFacPhone [varchar](50),
    @strFacFax [varchar](50)

    AS INSERT INTO [dbo].[tblAgency]

    ([strAgencyName])

    VALUES

    ( @AgencyName )


    SET @fkAgency = CAST(SCOPE_IDENTITY() AS INT)

    INSERT INTO [dbo].[tblFacility]

    (
    fkAgency,
    strFacName,
    strFacStreet,
    strFacCity,
    strFacState,
    strFacZip,
    strFacPhone,
    strFacFax
    )

    VALUES
    (
    @fkAgency,
    @strFacName,
    @strFacStreet,
    @strFacCity,
    @strFacState,
    @strFacZip,
    @strFacPhone,
    @strFacFax
    )
    GO

    EXECUTE [dbo].[sp_insert_agency] value4
    GO

    <!-- End Code -->

    Also... if anyone wants to point me in the direction for examples, tutorials or resources, I would like to evolve this SP into a very generic object and feed it the table names, fields, values to do ANY (most) insert(s) in the app. I understand I am going to have to use something called vectoring to define those parameters applicatin wide. I originally thoght I could could simply declare them all in a single constants file. I am entering the T-SQL/SP world with a limited understanding of syntax and contruct obatined primarily via ASP and ADO.

    Thanks Folks - Brad

    I have refrenced this post as source for the SCOPE_IDENTITY() idea:

    http://dbforums.com/showthread.php?t...ored+Procedure

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    I guess the line

    EXECUTE [dbo].[sp_insert_agency] value4

    fails since the procedure is defined as having more than one parameter,
    and you're just supplying one in the above call?

Posting Permissions

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