SQLTeam.com | Weblogs | Forums

SQL looping

#1

Hello

How can I express:

  1. Store each column in a variable [specify columns in a flexible way, i.e. all from a specific table or all matching %something% etc]
  2. Loop through the variable values and do something [like group by etc]
  3. Return results in separate tables or append one after the other

Thanks!

#2

loop is seldom recommended. express your desire with data sample using DDL and DML as well as your desired output with DDL and DML.

1 Like
#3

Thanks, I can try with an example but please note the concept rather than the specific example which can be achieved with a workaround. I am mostly looking at it as a general concept that can be expanded to various 'data operations' on a loop of values, rather than limited to this example.

Value1 Value2 Value3
1 1 1
2 1 1
3 1 2

For each of Value1, Value2, Value3
Count instances of '1'
Return to separate results sets (SSMS), i.e:

Count of '1' in Value1
1

Count of '1' in Value2
3

Count of '1' in Value3
2

The goal is that the variables through which the 'count instances of 1' will be calculated, should be dynamic rather than specifying Value1, Value2, Value3 or even *

#4

you are already thinking in terms of a specific solution: variables and loop which will lead one in the wrong direction.

state your requirements in plain English then. What I might be understanding is this

for any given table and any given variable find the count in all columns of the value of the variable sent on the specified table

#5

let us start with this.
Run this locally and see what you can glean that might lead you to the solution you need.
understanding is better than copy pasta code.


declare @search varchar(50) = 'address'
--sample table
create table sqlfor(id int, name varchar(50), 
yadi bit, yada datetime)

--sample data
insert into sqlfor
select top 10000  column_id, 
name, 1, dateadd(dd,user_type_id, getdate())
  from sys.all_columns

 select c.name, c.user_type_id, ty.name
   from sys.tables t 
   join sys.columns c 
     on t.object_id = c.object_id
	join sys.types ty on ty.user_type_id = c.user_type_id
  where t.name = 'sqlfor'
  and ty.name not in ('int','bit','datetime')

 drop table sqlfor
#6

Based on the type of questions you post - it seems you want to use SQL (the language) in ways it was not meant to be utilized. SQL (the language) is a data manipulation language - it is not a programming language.

If you want to build a solution using programming concepts - you would be much better looking at C# and developing it there instead of trying to figure out ways around using SQL.

1 Like