T-SQL : use an array like in a while loop

Question

I want to loop a array of chars in a WHILE loop (with only two values : 'C' & 'P') and use this variable in a SQL statement.

PSEUDO CODE:

WHILE SELECT 'C' UNION SELECT 'P'
BEGIN
    SELECT @Var -- Do real sql-statement here
END

I've this working code, but I was wondering if this can be written better / easier / more elegantly ?

DECLARE @Var CHAR(1)
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 2
BEGIN
  SELECT @Var = 
    CASE @counter
        WHEN 0 THEN 'C'
        ELSE 'P'
    END

  SELECT @Var -- Do real sql-statement here
  SET @counter = @counter + 1
END

To clarify, the real sql-statement is something like:

INSERT INTO MyTable
    SELECT A, B, @Var FROM AnotherTable WHERE ExportStatus = 'F'

It really does depend on the real sql that you are trying to do. Generally you should be able to avoid loops, but not always. If this really does require a loop, what you have is fine. Or, you could consider a FAST_FORWARD READONLY Cursor and loop through your input data with that. — MatBailie
– MatBailie, Commented Feb 4, 2012 at 9:27

Kirill Polishchuk · Accepted Answer · 2012-02-04 09:44:20Z

2

Simply use:

INSERT INTO MyTable
    SELECT * FROM AnotherTable WHERE ExportStatus 
        IN (SELECT 'C' UNION ALL SELECT 'P')

answered Feb 4, 2012 at 9:44

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ruzbeh Irani · Accepted Answer · 2012-02-04 13:41:19Z

0

Just insert it into the table.

Set operation perform better in SQL and A array can be placed into a table and operation can be performed on the table.

answered Feb 4, 2012 at 13:41

Ruzbeh Irani

2,4561 gold badge19 silver badges10 bronze badges

Collectives™ on Stack Overflow

T-SQL : use an array like in a while loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related