Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Unhappy Unanswered: Insert Proc With Both Select And Values

    I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.

    I created a test in MS Access and it loooks like this:

    INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
    SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
    FROM Country

    This works great in Access but not in SQL Server. In SQL Server 2 = @PatientTripID

    ANY SUGGESTIONS ON HOW TO HANDLE THIS?
    JayD
    Boulder, CO, USA

  2. #2
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    Hey, I tested your script. It works for me. Could you specify the error message and under what circumstance you are running this command and fail?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you looking for something more like:
    Code:
    CREATE PROCEDURE dbo.s2164
       @piPatientTripID INT
    AS
    
    INSERT INTO PatientTripRegionCountry_Temp (
       CountryID, RegionID
    ,  Country, PatientTripID)
       SELECT Country.CountryID, Country.RegionID
    ,     Country.Country, @PatientTripID
          FROM Country
    
    RETURN
    -PatP

  4. #4
    Join Date
    Apr 2004
    Posts
    12
    This is my Stored Proc. It executes but the field PatientTripID is set to <Null>

    CREATE PROCEDURE [dbo].[sp_PatientTripRegionCountryTemp_Insert_ForRegionID]
    @RegionID int,
    @PatientTripID int,
    @PatientID int
    AS
    INSERT INTO PatientTripRegionCountry_Temp ( CountryID, Country, RegionID, PatientTripID )
    SELECT C.CountryID, C.Country, C.RegionID, @PatientTripID
    FROM Country C
    WHERE (RegionID=@RegionID)
    GO

    Any Suggestions?
    JayD
    Boulder, CO, USA

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When you execute it from Query Analyzer, it should show "N row(s) affected" when it executes. Zero would be a bad thing in this case.

    -PatP

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    ?? How are you calling the procedure? Can you give a couple examples?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Apr 2004
    Posts
    12

    Thumbs up

    Thanks for all your help

    Don't ask me why, but I retried the versions shown in #4 above and this time it worked.
    JayD
    Boulder, CO, USA

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Way more gooder yet even! Glad you are back in business.

    -PatP

Posting Permissions

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