Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37

    Unanswered: cycling through results of a select statement

    I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.

    So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @nameofcompany. @nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.

    Like I said I am new and this is probably very simple but i need a little help getting started

    thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    SELECT * 
      FROM myTable99 o 
     WHERE EXISTS (   SELECT Company_Name 
    		    FROM myTable99 i
    		   WHERE o.Company_Name = i.Company_Name
    		GROUP BY Company_Name
    		  HAVING COUNT(*) > 1)
    But you wouldn't have to do that if you defined the table like

    Code:
    CREATE TABLE myTable99(Company_Name varchar(50) UNIQUE)
    EDIT: Where in Jersey? And what school?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37
    I am originally from Vernon (Mountain Creek). Went to school at Stevens Institute of Technology in Hoboken, NJ.

    I didn't create the dll for this database so i just loaded the schema by the .sql file. Right now I am handed an excel template and i wrote somce vb code to go through that excel file pull out the information i want and then write it to a text file delimited with "#" and then i load it into SQL server with a bulk load command. If the user sends me a template that already has duplicate entries in it and i try to load the data into SQL column that has a unique indentifier what will happen? Will it throw an error? If this is the case then it would probably be better to get the data in the database and then decided whether or not it is a duplicate.
    Redefining the table seems like the simplest way to go but i don't want to break functionality in the process

    thanks

  4. #4
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37
    I just tried to add this code and it is complaining on the second line in reference to the o. Here is the error: Error 170: Line 2: incorrect syntax near 'o'. As I said i am new to stored procedures and T-SQL do i need to declare the o and i as variables somewhere?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I didn't test the code, so you gave me a start...but the code does work...

    Where are you running this from? Do you have query analyzer and the other sql server client toools?

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99 (Company_Name varchar(50))
    GO
    
    INSERT INTO myTable99(Company_Name)
    SELECT 'Vernon Valley' UNION ALL
    SELECT 'Mountain Creek' UNION ALL
    SELECT 'Hidden Valley' UNION ALL
    SELECT 'Campgaw' UNION ALL
    SELECT 'Break Neck Road' UNION ALL
    SELECT 'High Point' UNION ALL
    SELECT 'Octogon Lounge' UNION ALL
    SELECT 'Great Gorge' UNION ALL
    SELECT 'Mountain Creek'
    GO
    
    
    SELECT * 
      FROM myTable99 o 
     WHERE EXISTS (   SELECT Company_Name 
    		    FROM myTable99 i
    		   WHERE o.Company_Name = i.Company_Name
    		GROUP BY Company_Name
    		  HAVING COUNT(*) > 1)
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    37
    sorry i am an a** i have an extra space floating in there. Man i am an idiot

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey...you're from Jersey...never apologize
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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