SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL and Stored Procedure Trouble

    I'm trying to create a stored procedure in MS SQL Server 2000 that will add orders to two tables at a time. I've simplified the procedure here to better illustrate my problem. Basically, I want to pass parameters to the procedure and use them to insert new records into my tables. Each table has an identity column and I'm using @@identity to retrieve the last identity number from Table A and add it to Table B. I think the ASP is fine -- I'm adding the parameters via the Command object -- so I don't think that is the problem. I think the problem lies somewhere with the order in which I'm passing the variables. I've tried this several different ways and this is just my latest attempt.

    Here is the procedure:
    Code:
    CREATE PROCEDURE dbo.sp_InsertTransaction
    	@TransCode varchar(255),
    	@TransactionID int
    AS
    INSERT INTO orderstest(TransCode, TransTime, AuthCode, CustID)
      VALUES(@TransCode, GETDATE(), 'test', 'test')
    
    SELECT @TransactionID = @@identity
    
    INSERT INTO orderstesttwo(ID) VALUES(@TransactionID)
    GO
    and i'm getting this error:

    Procedure 'sp_InsertTransaction' expects parameter '@TransactionID', which was not supplied.

    Keep in mind, I have created the parameter @TransCode in my ASP code.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    You've got the @TransactionID setup as being passed into the StoredProc. Move it AFTER the AS (removing the comma after the @TransCode line and change the @TransactionID line to
    declare @TransactionID as Int
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dave-

    I was thinking something like that. I actually already tried using DECLARE on the variable but I did it before the AS statement. I'll try again.

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    thanks

    Dave-

    It worked like a charm ... thanks.

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Glad to help....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

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