Select columns into an array in SQL Server Stored Procedure

Question

My table consists of multiple phone number columns and I want to select multiple columns (say phone numbers) into an array for my store procedure. I am trying to use SELECT but it returns only one value as shown in the sample code

DECLARE @phone VARCHAR(15)
 SELECT @phone = phone1 FROM AddressTable
PRINT @phone

Now I want get the values of more than one column value into one variable. I know for this purpose we normally use arrays. But I am not sure how to use. Any Help...!!!

• sgeddes: Are you just trying to concatenate multiple columns into a single variable? SELECT @phone = phone1 + ', ' + phone2 ...
• Gordon Linoff: @Vikram . . . What are you actually trying to accomplish? The table format might be quite sufficient to solve your problem.
• Vikram: I am trying to verify the phone number format for 5 columns. I am using tsqlt. I used table format and I found its helpful. But I need to compare those results with a unique number
• Gordon Linoff: @Vikram . . . Why do you need all the numbers at once for verification? Just validate one phone number at a time.
• Vikram: I need to test with different permutations so testing all the phone numbers columns for each test with unique phone number. which will finish off 8 tests for 5 fields.

Answer 1

SQL doesn't support arrays. You can use something like a table variable or a comma delimited string

SELECT INTO a table variable in T-SQL

• Vikram: You mean append all the phone numbers into a string?
• TGH: I would go with the table variable, but you could go the delimited string route as well... Or xml or some other "multi" value format