Re: Stored Procedure Enter Same Record Twice?

From: Leon (vnality_at_msn.com)
Date: 11/19/04 

Date: Fri, 19 Nov 2004 12:18:21 -0600

could you show me an example of 3 or 4 joined tables please.

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:u4zNUNmzEHA.2192@TK2MSFTNGP14.phx.gbl...
> Leon wrote:
>> I'm kind of new to sql server 2000. how should I go about updating my
>> join syntax to the ANSI style. like this example..
>> CREATE PROCEDURE GetStudentDetail
>> @AccountID INT
>> AS
>> SELECT A.Username, A.Password, A.SecretQuestion, A.SecretAnswer,
>> A.RoleID, A.Active, S.FirstName, S.LastName,
>> S.BirthMonth, S.BirthDay, S.BirthYear, S.Gender, S.SchoolState,
>> S.SchoolName, S.Classification, S.Major, S.EmailAddress
>>
>> FROM Account A INNER JOIN Student S ON A.AccountID = S.AccountID
>> WHERE A. AccountID = @AccountID
>> GO.
>>
>> "David Gugick" <davidg-nospam@imceda.com> wrote in message
>> news:e8E5iPlzEHA.3784@TK2MSFTNGP12.phx.gbl...
>>> Leon wrote:
>>>> I know this stored procedure looks like am just playing around with
>>>> redundant data, but it's necessary; however, can anyone tell me why
>>>> the following stored procedure enter the same first winner twice,
>>>> then enter the same second winner twice, then enter the same third
>>>> winner twice, and on and on...Thanks!
>>>>
>>>> CREATE PROCEDURE AddWinner
>>>> AS
>>>> INSERT Winner
>>>> (DrawingID,TicketID,TicketCreationDate,QuickPick,AccountID,AccountCreationDate,EmailAddress,FirstName,
>>>> LastName,
>>>> BirthMonth,BirthDay,BirthYear,Gender,SchoolState,SchoolName,Classification,Major)
>>>>
>>>> SELECT
>>>> DS.CurrentDrawing,T.TicketID,T.PickDate,T.QuickPick,S.AccountID,S.ActivationDate,S.EmailAddress,S.FirstName,
>>>> S.LastName,
>>>> S.BirthMonth,S.BirthDay,S.BirthYear,S.Gender,S.SchoolState,S.SchoolName,S.Classification,S.Major
>>>>
>>>> FROM DrawSetting AS DS,Ticket AS T, Student AS S,Drawing AS D
>>>>
>>>> WHERE T.Active = 1 AND T.DrawingID = DS.CurrentDrawing AND
>>>> D.DrawingID = DS.CurrentDrawing
>>>> AND T.Num1 = D.Num1
>>>> AND T.Num2 = D.Num2
>>>> AND T.Num3 = D.Num3
>>>> AND T.Num4 = D.Num4
>>>> AND T.Num5 = D.Num5
>>>> AND T.Num6 = D.Num6
>>>
>>> Have you run just the select portion of the insert statement with
>>> some test data to see what is returned?
>>>
>>> I see 4 tables: DS, T, S, and D
>>> I see only 2 joins: T to DS and D to DS (where is S???)
>>>
>>> It looks like you have a select problem, so verify the select works
>>> correctly before you apply it to the insert operation. You should
>>> also update your join syntax to the ANSI style. It will be more
>>> compliant and be easier to read.
>>>
>>>
>>> --
>>> David Gugick
>>> Imceda Software
>>> www.imceda.com
>
> You can wait on the ANSI conversion. Just get the select working properly
> first using the old style syntax. It's just that the ANSI syntax makes it
> more difficult to leave out joins. Generally, all your tables need to
> appear in some join condition. If you have four tables, then you at least
> three joins to join them. I see a T to D join I missed earlier, but I'm
> still not seeing a join anywhere to S. Why it that table not joined
> anywhere?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Relevant Pages