+ Post New Thread
Results 1 to 2 of 2
Coding Thread, SQL Insert Problem in Coding and Web Development; Hi there, I have created an insert statement to create a new record in a database. This is used from ...
  1. #1
    Shrimpersfan's Avatar
    Join Date
    Feb 2008
    Location
    Peterborough
    Posts
    52
    Thank Post
    9
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    SQL Insert Problem

    Hi there, I have created an insert statement to create a new record in a database. This is used from a task automation program and the client record is mapped using that so records ARE created on the correct client but i only want 1 record to be created, at the moment if a client has 10 quotes then it will insert 10 new quotes instead of just 1. My code is below:

    CREATE PROCEDURE [dbo].[ietsp_CreateDODQuotes]

    @ContactID AS INT,
    @TaxYear AS INT

    AS

    SELECT
    @TaxYear = TaxProYear
    FROM
    FlightDeckTest.dbo.iet_TaxYears ty
    WHERE
    IsCurrent = 'T'

    /** Add PTM DOD Quote **/
    BEGIN
    INSERT INTO FlightDeckTest.dbo.Lead
    (
    ContactID,
    Owner,
    Status,
    EntitySubType,
    ietCreateDate,
    ietMumbaiStatus,
    ietTaxAdmin,
    ietTaxYear,
    ietQuoteIncl,
    ietQuoteExcl,
    ietVAT,
    ietProdServID,
    ietProdServDesc,
    ietPayMethod,
    ietCardType,
    ietCardHolder,
    ietCardNumber,
    ietAcctNo,
    ietSortCode,
    ietIssueNumber,
    ietSecurityCode,
    ietInstallments,
    ietMonthRepay,
    ietFinalWithIns,
    ietFinalInstallment,
    ietAcctName,
    ietBankName
    )

    SELECT
    l.ContactID,
    l.Owner,
    'AWAUTH',
    l.entitySubType,
    GETDATE(),
    0,
    l.ietTaxAdmin,
    @TaxYear,
    l.ietQuoteIncl + CONVERT(DECIMAL(7,2),176.25),
    l.ietQuoteExcl,
    l.ietVAT,
    l.ietProdServID,
    'PTM DOD - ie taxguard',
    'INVTP',
    l.ietCardType,
    l.ietCardHolder,
    l.ietCardNumber,
    l.ietAcctNo,
    l.ietSortCode,
    l.ietIssueNumber,
    l.ietSecurityCode,
    l.ietInstallments,
    l.ietMonthRepay,
    l.ietFinalWithIns,
    l.ietFinalInstallment,
    l.ietAcctName,
    l.ietBankName
    FROM
    FlightdeckTest.dbo.Lead l
    INNER JOIN
    FlightDeckTest.dbo.Contact c ON
    l.ContactID = c.ContactID
    WHERE
    ((l.ietProdServDesc LIKE '%PTM%' OR l.ietProdServDesc LIKE '%Tax Return%')) AND
    (l.Status = 'ACT') AND
    (l.ietTaxYear IN (SELECT TaxProYear FROM iet_TaxYears WHERE IsCurrent = 'T'))

    END
    GO

    Any help would be greatly appreciated.

  2. #2
    ICTNUT's Avatar
    Join Date
    Jul 2005
    Location
    Hereford
    Posts
    1,419
    Thank Post
    196
    Thanked 249 Times in 122 Posts
    Rep Power
    62
    Shrimpersfan:

    If I read this correctly you are passing into your Stored Procedure the ContactID and TaxYear:

    @ContactID AS INT,
    @TaxYear AS INT
    You have defined @TaxYear as an Integer but then you are trying to change it to something else here:

    @TaxYear = TaxProYear
    Where does TaxProYear come from?

    Your INSERT statement has no where clause so this would account for ALL quotes being added.

    I dont understand what the last SELECT statement is for as you are not actually outputting anything back.

    Does this make sense

SHARE:
+ Post New Thread

Similar Threads

  1. SQL Insert Query
    By Pashers in forum Web Development
    Replies: 1
    Last Post: 9th September 2008, 03:42 PM
  2. SQL Server 2005 Backups Problem
    By ICTNUT in forum Windows
    Replies: 0
    Last Post: 7th July 2008, 09:53 AM
  3. WiseSoft ScriptBuilder + SQL problem
    By j17sparky in forum Windows
    Replies: 2
    Last Post: 9th February 2008, 06:33 PM
  4. Problem after SQL Migration
    By Jawloms in forum MIS Systems
    Replies: 5
    Last Post: 24th October 2007, 03:06 PM
  5. Library Management System - SQL Problem
    By AnnDroyd in forum Network and Classroom Management
    Replies: 0
    Last Post: 2nd October 2007, 08:45 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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