CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36621

How to iterate through each row in sql server?

My query returns 26 table names.

select name from sys.tables where name like '%JPro_VP_Service%'

Now I'm trying to write a query to check in every table return from the above query.

--consider this is my first table 
select * from JPro_VP_Service 
where row_id like '%1-101%' or row_id like '%1-102%'
-- likewise I want to search in 26 tables return from above query

I think I need to write for or cursor to accomplish this.

Can anyone help me how to achieve this?

Upvotes: 2

Views: 12899

Answers (4)

Question3CPO
Question3CPO

Reputation: 1202

This route may work, though you might want the results saved to a table:

DECLARE @tables TABLE(
ID INT IDENTITY(1,1),
Name VARCHAR(100)
)

INSERT INTO @tables (Name)
SELECT name 
FROM sys.tables 
WHERE name like '%JPro_VP_Service%'

DECLARE @b INT = 1, @m INT, @table VARCHAR(100), @cmd NVARCHAR(MAX)
SELECT @m = MAX(ID) FROM @tables

WHILE @b <= @m
BEGIN

SELECT @table = Name FROM @tables WHERE ID = @b

SET @cmd = 'select * from ' + @table + '
where row_id like ''%1-101%'' or row_id like ''%1-102%''
'

EXECUTE sp_executesql @cmd

SET @b = @b + 1
SET @cmd = ''

END

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

The easiest way to do this is Try this:

SELECT 'select * from ' + name 
       + ' where row_id         like ''%1-101%'' or row_id like ''%1-102%''' 
FROM   sys.tables 
WHERE  name LIKE '%JPro_VP_Service%'

you will get all tables together with the same conditions. You could execute them together.

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13496

DECLARE @mn INT 
DECLARE @mx INT 
DECLARE @tblname VARCHAR(100); 

WITH cte 
     AS (SELECT Row_number() 
                  OVER ( 
                    ORDER BY (SELECT 0)) AS rn, 
                name 
         FROM   sys.tables 
         WHERE  name LIKE '%JPro_VP_Service%') 
SELECT @mn = Min(rn), 
       @mx = Max(rn) 
FROM   cte 

WHILE( @mn >= @mx ) 
  BEGIN 
      SELECT @tblname = name 
      FROM   cte 
      WHERE  rn = @mn 

      SELECT * 
      FROM   @tblname 
      WHERE  row_id LIKE '%1-101%' 
              OR row_id LIKE '%1-102%' 

      --Do something else 
      SET @mn=@mn + 1 
  END

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

Yes, you would have to use a cursor for this, and probably also dynamic sql

Also see

Generate dynamic SQL statements in SQL Server

Dynamic SQL PROs & CONs

Upvotes: 0

Related Questions