Problem with stored procedure


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Problem with stored procedure

Hybrid View

  1. #1
    yip Guest

    Problem with stored procedure


    Hi,

    I'm getting an error on line 12 of this stored procedure that I wrote. Any
    suggestions? (Of course, this posting is not word wrapped)


    CREATE PROCEDURE YipReport_SP_BloodDonation
    (
    @name varchar(80),
    @newic varchar(15),
    @oldic varchar(8),
    @donationdate datetime
    )
    As
    Begin

    Select DonorId.name as Nama, DonorId.newIC as ICBaru, DonorId.oldIC as
    ICLama, DonorCategories.category as Categories, DonorId.passportNum as NoPassport,

    Donors.dateOfBirth as TarikhLahir, Donors.sex as Jantina, Donors.occupation
    as Pekerjaan, Races.race as Keturunan, MaritalStatus.maritalStatus as TarafPerkahwinan,

    AlamatRumah.address1 as AlamatRumah1, AlamatRumah.address2 as AlamatRumah2,
    AlamatRumah.address3 as AlamatRumah3, AlamatRumah.address4 as AlamatRumah4,

    AlamatRumahBandar.town as AlamatBandarRumah, AlamatRumahNegeri.state
    as AlamatNegeriRumah, AlamatRumahNegara.country as AlamatNegaraRumah,
    AlamatRumah.postcode as AlamatPostcodeRumah, AlamatRumah.homePhone
    as NoTelephoneRumah, AlamatRumah.cellularPhone AS NoTelephoneBimbitRumah,


    AlamatPejabat.address1 as AlamatPejabat1, AlamatPejabat.address2 as
    AlamatPejabat2, AlamatPejabat.address3 as AlamatPejabat3, AlamatPejabat.address4
    as AlamatPejabat4,
    AlamatPejabatBandar.town as AlamatBandarPejabat, AlamatPejabatNegeri.state
    as AlamatNegeriPejabat, AlamatPejabatNegara.country as AlamatNegaraPejabat,

    AlamatPejabat.postcode as AlamatPostcodePejabat, AlamatPejabat.officePhone
    as NoTelephonePejabat,

    donorQuestStd.q1HealthyToday as Soalan1, donorQuestStd.q2Problems as
    Soalan2, donorQuestStd.q2ProblemsText as Soalan2Text, donorQuestStd.q3aAspirins
    as Soalan3a,
    donorQuestStd.q3bMedications as Soalan3b, donorQuestStd.q3bMedicationsText
    as Soalan3bText, donorQuestStd.q3cFlu as Soalan3c, donorQuestStd.q3dOtherInfections
    as Soalan3d,
    donorQuestStd.q3dOtherInfectionsText as Soalan3dText, donorQuestStd.q4Immunisation
    as Soalan4, donorQuestStd.q4ImmunisationsText as Soalan4Text, donorQuestStd.q5Dental
    as Soalan5,
    donorQuestStd.q6Tattoo as Soalan6, donorQuestStd.q7Treatments as Soalan7,
    donorQuestStd.q7TreatmentsText as Soalan7Text, donorQuestStd.q8aJaundice
    as Soalan8a,
    donorQuestStd.q8bHepB as Soalan8b, donorQuestStd.q8cHepC as Soalan8c, donorQuestStd.q8dAsthma
    as Soalan8d, donorQuestStd.q8eTB as Soalan8e, donorQuestStd.q8fVenereal as
    Soalan8f,
    donorQuestStd.q8gMalaria as Soalan8g, donorQuestStd.q8hDiabetes as
    Soalan8h, donorQuestStd.q8iHypertension as Soalan8i, donorQuestStd.q8jHeart
    as Soalan8j,
    donorQuestStd.q8kMental as Soalan8k, donorQuestStd.q8lFits as Soalan8l,
    donorQuestStd.q9OtherDiseases as Soalan9, donorQuestStd.q9OtherDiseasesText
    as Soalan9Text,
    donorQuestStd.q10aFamilyHepBHepC as Soalan10a, donorQuestStd.q10bFamilyMental
    as Soalan10b, donorQuestStd.q11aPregnant as Soalan11a, donorQuestStd.q11bPeriod
    as Soalan11b,
    donorQuestStd.q12aTranfusion as Soalan12a, donorQuestStd.q12bOperation
    as Soalan12b, donorQuestStd.q13aHGHormone as Soalan13a, donorQuestStd.q13bCornea
    as Soalan13b,
    donorQuestStd.q13cDuramatter as Soalan13c, donorQuestStd.q14LivedUK
    as Soalan14, donorQuestStd.q15LivedEurope as Soalan15,

    YipSearchResult_VW_DonationFormonPDNKLCount.PDNKLCount,YipSearchResult_VW_DonationFormonOt hersCount.OthersCount,
    YipSearchResult_VW_DonationFormonLastDonationDate.LastDonationDate,YipSearchResult_VW_Dona tionFormonBloodOrAphaerisis.donationType,

    right(left(getdate(),11),4) as CurrentDate, right(left(Donors.dateOfBirth,11),4)
    as DateofBirthInYear , ('19'+left(DonorId.newIC,2)) As NewICInYear

    From dbo.YipSearchResult_VW_DonationFormOnPDNKLCount RIGHT OUTER JOIN dbo.YipSearchResult_VW_DonationFormOnOthersCount

    RIGHT OUTER JOIN dbo.YipSearchResult_VW_DonationFormOnLastDonationDate

    RIGHT OUTER JOIN dbo.YipSearchResult_VW_DonationFormOnBloodOrAphaerisis

    RIGHT OUTER JOIN dbo.DonorId
    ON dbo.YipSearchResult_VW_DonationFormOnBloodOrAphaerisis.name
    = dbo.DonorId.name
    AND dbo.YipSearchResult_VW_DonationFormOnBloodOrAphaerisis.newIC
    = dbo.DonorId.newIC
    AND dbo.YipSearchResult_VW_DonationFormOnBloodOrAphaerisis.oldIC
    = dbo.DonorId.oldIC

    ON dbo.YipSearchResult_VW_DonationFormOnLastDonationDate.name =
    dbo.DonorId.name
    AND dbo.YipSearchResult_VW_DonationFormOnLastDonationDate.newIC
    = dbo.DonorId.newIC
    AND dbo.YipSearchResult_VW_DonationFormOnLastDonationDate.oldIC
    = dbo.DonorId.oldIC
    ON dbo.YipSearchResult_VW_DonationFormOnOthersCount.name = dbo.DonorId.name

    AND dbo.YipSearchResult_VW_DonationFormOnOthersCount.newIC = dbo.DonorId.newIC

    AND dbo.YipSearchResult_VW_DonationFormOnOthersCount.oldIC = dbo.DonorId.oldIC
    ON dbo.YipSearchResult_VW_DonationFormOnPDNKLCount.name =
    dbo.DonorId.name
    AND dbo.YipSearchResult_VW_DonationFormOnPDNKLCount.newIC = dbo.DonorId.newIC

    AND dbo.YipSearchResult_VW_DonationFormOnPDNKLCount.oldIC = dbo.DonorId.oldIC

    LEFT OUTER JOIN dbo.Towns AlamatRumahBandar
    INNER JOIN dbo.DonorAddresses AlamatRumah
    ON AlamatRumahBandar.id = AlamatRumah.townFid
    INNER JOIN dbo.States AlamatRumahNegeri
    ON AlamatRumahBandar.stateFid = AlamatRumahNegeri.id
    INNER JOIN dbo.Countries AlamatRumahNegara
    ON AlamatRumahNegeri.countryFid = AlamatRumahNegara.id
    ON dbo.DonorId.id = AlamatRumah.donorFid
    LEFT OUTER JOIN dbo.Towns AlamatPejabatBandar
    INNER JOIN dbo.DonorEmpAddr AlamatPejabat
    ON AlamatPejabatBandar.id = AlamatPejabat.townFid
    INNER JOIN dbo.States AlamatPejabatNegeri
    ON AlamatPejabatBandar.stateFid = AlamatPejabatNegeri.id
    INNER JOIN dbo.Countries AlamatPejabatNegara
    ON AlamatPejabatNegeri.countryFid = AlamatPejabatNegara.id
    ON dbo.DonorId.id = AlamatPejabat.donorFid
    LEFT OUTER JOIN dbo.Donations
    INNER JOIN dbo.DonorsBloodBanks
    ON dbo.Donations.donorBloodBankFid = dbo.DonorsBloodBanks.id
    LEFT OUTER JOIN dbo.donorQuestStd
    ON dbo.Donations.id = dbo.donorQuestStd.donationFid
    ON dbo.DonorId.id = dbo.DonorsBloodBanks.donorFid
    LEFT OUTER JOIN dbo.DonorCategories
    INNER JOIN dbo.Donors
    ON dbo.DonorCategories.id = dbo.Donors.donorCategoryFid
    INNER JOIN dbo.Races
    ON dbo.Donors.raceFid = dbo.Races.id
    INNER JOIN dbo.MaritalStatus
    ON dbo.Donors.maritalStatusFid = dbo.MaritalStatus.id
    ON dbo.DonorId.id = dbo.Donors.DonorFid

    Where DonorId.name=@name and DonorId.newIC=@newic and DonorId.oldIC=@oldic
    and Donations.donationdate=@donationdate

    End

  2. #2
    Rune Bivrin Guest

    Re: Problem with stored procedure

    "yip" <vb@127.0.0.1> wrote in news:3d8e9546$1@10.1.10.29:

    >
    > Hi,
    >
    > I'm getting an error on line 12 of this stored procedure that I
    > wrote. Any
    > suggestions? (Of course, this posting is not word wrapped)
    >
    >

    Yes, it was...
    Anyway, include the table definitions, or it will be quite hard to diagnose
    it. Also, whenever trying to get help, tell us *what* error you're getting.
    That saves us time.

    --
    Rune Bivrin
    - OOP since 1989
    - SQL Server since 1990
    - VB since 1991


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center