Sunday, March 25, 2012

Queries in While Condition

When using a query in the condition of a while loop, is that query being performed each time?
EX:
While (@.X <= (Select count(*) from SOME_TABLE))I was going to say no...but...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E'
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < (SELECT COUNT(*) FROM myTable99)
BEGIN
SELECT @.x = @.x + 1
END
SELECT @.x

SELECT @.x = 1
WHILE @.x < (SELECT COUNT(*) FROM myTable99)
BEGIN
IF @.x IN (1,3,5) INSERT INTO myTable99(Col2) SELECT 'x'
SELECT @.x = @.x + 1
END
SELECT @.x
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

No comments:

Post a Comment