SQL For Loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL For Loop

Author

Topic

dmgotty
Starting Member

4 Posts

Posted - 2012-05-18 : 10:40:27

Hello everyone,

I am trying to write a SQL Query with a for loop that uses the variable itself within the filter.

Here is an example that does not work, that im sure will help you understand what im looking to do..

For i = 0 to 4
SELECT *
FROM tablename
WHERE ColumnValue = i
next

Thanks in advance for any help you can give

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-18 : 10:42:10

SQL Server doesn't support FOR loops, and why are you looping?

SELECT * FROM TableName WHERE ColumnValue BETWEEN 1 AND 4

SQL is not a procedural language, and looping constructs will typically lead to poor performance.

dmgotty
Starting Member

4 Posts

Posted - 2012-05-18 : 10:53:47

Hello Rob, thanks for the response...

I really want to write the following query in a smarter way...

I am hoping to pass a variable from a webpage to it which will tell the query how many times it should run... Hence why the for loop came to mind ^_^

Unfortunately, the situation does not allow for me to construct the SQL query through asp or php.

(SELECT TOP 1 PrimaryKey, ColumnName
FROM tablename WHERE (FilterColumn = x OR FilterColumn = y) AND intPosition = '1' ORDER BY FilterColumn ASC) a
UNION ALL SELECT PrimaryKey, ColumnName
FROM (SELECT TOP 1 PrimaryKey, ColumnName
FROM tablename WHERE (FilterColumn = x OR FilterColumn = y) AND intPosition = '2' ORDER BY FilterColumn ASC) b
UNION ALL SELECT PrimaryKey, ColumnName
FROM (SELECT TOP 1 PrimaryKey, ColumnName
FROM tablename WHERE (FilterColumn = x OR FilterColumn = y) AND intPosition = '3' ORDER BY FilterColumn ASC) c
UNION ALL SELECT PrimaryKey, ColumnName
FROM (SELECT TOP 1 PrimaryKey, ColumnName
FROM tablename WHERE (FilterColumn = x OR FilterColumn = y) AND intPosition = '4' ORDER BY FilterColumn ASC) d

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-18 : 11:57:44

Give this a shot:

DECLARE @high int;
SET @high=4;
WITH CTE(PrimaryKey, ColumnName, RowNum) AS (
	SELECT PrimaryKey, ColumnName, ROW_NUMBER() OVER (PARTITION BY intPosition ORDER BY FilterColumn ASC)
	FROM tablename
	WHERE FilterColumn IN (x,y) AND intPosition BETWEEN 1 AND @high)
SELECT PrimaryKey, ColumnName
FROM CTE
WHERE RowNum=1;

X002548
Not Just a Number

15586 Posts

Posted - 2012-05-18 : 16:27:16

What Version of SQL Server are you using?

X002548
Not Just a Number

15586 Posts

Posted - 2012-05-18 : 17:17:02

Well..you do have Loops


DECLARE @i int; SET @i = 0

WHILE @i <5
  BEGIN
	SELECT * FROM tablename WHERE ColumnValue = @i
	SET @i = @i + 1
  END

Which is really the same as


DECLARE @i int; SET @i = 0

SELECT * FROM Tablename WHERE ColumnValue BETWEEN IN (@i AND @i + 4)

Sort of

dmgotty
Starting Member

4 Posts

Posted - 2012-05-22 : 05:42:37

Hey guys,

Seems everything has worked out (for now ^_^)

Just wanted to say thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources