How to select each row into a variable in SQL Server

Question

We have a function that turns a delimited list into a table:

select * from dbo.fn_rpt_ParseValues('abc|123|test','|')

Results:

abc
123
test

How can I get each row into a SQL variable? For example:

@Name = 'abc'
@Phone = '123'
@Comment = 'test'

Thanks!

Very inefficiently...You should rethink your method. It will be more efficient to just put them into variables from the delimited string I suspect — JNK
– JNK, Commented May 14, 2012 at 13:50
Why are you trying to get them into variables anyway? You're not thinking of doing something with them in a loop are you? If so, parsing the list to a table and performing set based operations would be magnitudes faster. — Bridge
– Bridge, Commented May 14, 2012 at 13:55
The stored procedure returns rows back to a .NET data grid. There are other columns that are normal, but one is pipe delimited but I have to return them as individual columns. — user390480
– user390480, Commented May 14, 2012 at 14:06
Can you change fn_rpt_ParseValues? If so, just change it to return each value in a separate column, then you can assign them to variables in a single SELECT. — Pondlife
– Pondlife, Commented May 14, 2012 at 14:18

Mikael Eriksson · Accepted Answer · 2012-05-14 15:34:04Z

5

declare @S varchar(100) = 'abc|123|test'

declare @Name varchar(10)
declare @Phone varchar(10)
declare @Comment varchar(10)

select @Name = X.value('x[1]', 'varchar(10)'),
       @Phone = X.value('x[2]', 'varchar(10)'),
       @Comment = X.value('x[3]', 'varchar(10)')
from (select cast('<x>'+replace(@S, '|', '</x><x>')+'</x>' as xml)) as T(X)

select @Name, @Phone, @Comment

answered May 14, 2012 at 15:34

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Comments

Tim Schmelter · Accepted Answer · 2012-05-14 14:16:52Z

1

DECLARE @Split table(
     RowIndex int IDENTITY(1,1)
    ,Item varchar(200));
INSERT INTO @Split 
    SELECT * from dbo.fn_rpt_ParseValues('abc|123|test','|');

DECLARE @Name   varchar(50);
DECLARE @Phone  varchar(50);
DECLARE @Comment varchar(100);
SET @Name   =  (SELECT Item FROM @Split WHERE RowIndex=1);
SET @Phone  =  (SELECT Item FROM @Split WHERE RowIndex=2);
SET @Comment = (SELECT Item FROM @Split WHERE RowIndex=3);

answered May 14, 2012 at 14:16

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

1 Comment

MatBailie Over a year ago

Alternatively, pivot the results and then you can do SELECT @name = Field1, @Phone = Field2, etc?

Cade Roux · Accepted Answer · 2012-05-14 14:20:19Z

1

Assuming that the rows are ordered, you want to do a pivot to turn them into columns.

Another option is to have your parse function (or a modified version) return a single row result set (table-valued function) and use CROSS/OUTER APPLY to append them to a row:

http://msdn.microsoft.com/en-us/library/ms174853.aspx

answered May 14, 2012 at 14:20

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Collectives™ on Stack Overflow

How to select each row into a variable in SQL Server

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related