CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Vlad
Vlad

Reputation: 2773

Loop through rows of a query in T-SQL?

I have the following T-SQL script:

declare @Name nvarchar

declare data cursor for
select Name from MyDB.dbo.MyTable;

OPEN data;  

-- Perform the first fetch.  
FETCH NEXT FROM data;  

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.  
WHILE @@FETCH_STATUS = 0  
BEGIN  
   -- This is executed as long as the previous fetch succeeds.  

   FETCH NEXT FROM data INTO @Name;  
   Print 'Name: ' + @Name
END  
CLOSE data;  
DEALLOCATE data;  
GO

I want to make a script that will compare each of the strings in a first column with each of the strings in the second column.

The problem is, I don't know how to loop through each of the rows and take a separate string value. The code above prints only the first value in the query result.

What am I doing wrong?

Upvotes: 1

Views: 109

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

To compare all values from one column to all values in another column you don't need a cursor, a simple join will do the work - since you didn't provide sample data and also not desired results, I had to make my own:

Create and populate sample table (Please save us this step in your future questions)

CREATE TABLE MyTable 
(
    Id int identity(1,1),
    Name1 char(3),
    Name2 char(3)
)

INSERT INTO MyTable (Name1, Name2) VALUES
('abc','def'),('zyx','abc'),
('ghi','jkl'),('yza','ghi'),
('mno','pqr'),('nml','mno'),('pqr','qpo'),
('stu','vwx'),('wvu','tsr'),('kji','hgf')

The query:

SELECT T1.Id, T1.Name1, T1.Name2, T2.Id, T2.Name1, T2.Name2
FROM MyTable T1
JOIN MyTable T2 ON T1.Name1 = T2.Name2

Result:

Id  Name1   Name2   Id  Name1   Name2
1   abc     def     2   zyx     abc
3   ghi     jkl     4   yza     ghi
5   mno     pqr     6   nml     mno
7   pqr     qpo     5   mno     pqr

Upvotes: 2

Milney
Milney

Reputation: 6417

You probably don't want to use a Cursor.

Are your columns in the same table? If so this is as simple as this;

-- Show All rows with [DIFFERENT] Name and Name2 fields
SELECT 
    Name,
    Name2
FROM [MyDB].[dbo].[MyTable]
WHERE 
    Name <> Name2

-- Show All rows with [SAME] Name and Name2 fields
SELECT 
    Name,
    Name2
FROM [MyDB].[dbo].[MyTable]
WHERE 
    Name = Name2

If not you will need to post the table definitions and names of columns to get a more concrete example

Upvotes: 0

Related Questions