Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Question Unanswered: Simple query question

    I have a query where I am passing different parameters. If I run it in sql 2000 it works fine but when i run it in sql 2005 it gives me sometimes same record multiple times. can you please check if something is wrong with this query. it should return one record only once. Thats so strange why its returning one record multiple times. (not always but some records appear multiple times, they have same id).


    Following is my query:

    IF (@FirstName<>'') SELECT @FirstNameOP = 'EQ' ELSE SELECT @FirstNameOP = ''
    IF (@LastName<>'') SELECT @LastNameOP = 'EQ' ELSE SELECT @LastNameOP = ''
    IF (@City<>'') SELECT @CityOP = 'EQ' ELSE SELECT @CityOP = ''
    IF (@State<>'') SELECT @StateOP = 'EQ' ELSE SELECT @StateOP = ''

    SELECT
    S.*,
    D.Drug,
    C.SubCompanyCode

    FROM
    Speakers S
    left outer join SpeakerSubcompanies C on (S.SpeakerID = C.SpeakerID)
    left outer join SpeakerDrugs D on (S.SpeakerID = D.SpeakerID) and (C.SubCompanyCode=D.SubCompanyCode)
    left outer join ProgramTypeDrugs T on (C.SubCompanyCode=T.SubCompanyCode) and (T.IsPrimary = 1)

    WHERE


    CASE @SpeakerIDOP
    WHEN '' THEN 1
    WHEN 'EQ' THEN
    CASE
    WHEN S.SpeakerID = @SpeakerID THEN 1 ELSE 0
    END
    END=1 AND
    CASE @FirstNameOP
    WHEN '' THEN 1
    WHEN 'EQ' THEN
    CASE
    WHEN S.FirstName LIKE @FirstName+'%' THEN 1 ELSE 0
    END
    END=1 AND
    CASE @LastNameOP
    WHEN '' THEN 1
    WHEN 'EQ' THEN
    CASE
    WHEN S.LastName LIKE @LastName+'%' THEN 1 ELSE 0
    END
    END=1 AND
    CASE @CityOP
    WHEN '' THEN 1
    WHEN 'EQ' THEN
    CASE
    WHEN S.BusinessCity LIKE '%'+@City+'%' THEN 1 ELSE 0
    END
    END=1 AND
    CASE @StateOP
    WHEN '' THEN 1
    WHEN 'EQ' THEN
    CASE
    WHEN S.BusinessState = @State THEN 1 ELSE 0
    END
    END=1
    ORDER BY
    S.LastName, S.FirstName

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would take a close look for data discrepancies between the two databases. Either there are records in the 2005 database that cause the query to return duplicates, or the query naturally returns duplicates but there happen to be no duplicates in your 2000 database.
    In them meantime, dump the "SELECT S.*" and list all your columns instead. "SELECT *" is very bad practice for production code.
    Also, rewrite your WHERE clause to eliminate the CASE statements and your code will much clearer:
    Code:
    SELECT	S.*,
    	D.Drug,
    	C.SubCompanyCode
    FROM	Speakers S
    	left outer join SpeakerSubcompanies C on S.SpeakerID = C.SpeakerID
    	left outer join SpeakerDrugs D
    		on S.SpeakerID = D.SpeakerID
    		and C.SubCompanyCode=D.SubCompanyCode
    	left outer join ProgramTypeDrugs T
    		on C.SubCompanyCode=T.SubCompanyCode
    		and T.IsPrimary = 1
    WHERE	(@SpeakerIDOP = ''
    		OR (@SpeakerIDOP = 'EQ' AND S.SpeakerID = @SpeakerID))
    	AND (@FirstNameOP = ''
    		OR (@FirstNameOP = 'EQ' AND S.FirstName LIKE @FirstName+'%'))
    	AND ((@LastNameOP = ''
    		OR @LastNameOP = 'EQ' AND S.LastName LIKE @LastName+'%'))
    	AND (@CityOP = ''
    		OR (@CityOP = 'EQ' AND S.BusinessCity LIKE '%'+@City+'%'))
    	AND (@StateOP = ''
    		OR @StateOP = 'EQ' AND S.BusinessState = @State))
    ORDER BY S.LastName,
    S.FirstName
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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