Retrieve multiple pieces of data from a single SQL Server variable

Question

I have two variables like:

@FieldName 
@values

Those two variables hold values like:

@FieldName - contains [a],[b],[c],[d]
@values - contains 5,6,7,8

Now I need to retrieve the data of column 'b' & 'd' only.

How can we get b=6 & d=8?

Thanks in advance.

are you forming the values of these variables elsewhere and passing them to this stored procedure? — Vikdor
– Vikdor, Commented Oct 22, 2012 at 5:14
Basically, from a long stored procedure using a select command i get those value .Now need to split field and values. I don't pass the parameter to anywhere.Just use it to create a INSERT/UPDATE command — riad
– riad, Commented Oct 22, 2012 at 5:17
If you are on SQL Server 2005 or higher, I recommend using XML to hold those values, which will make it easier to generate resultsets to directly insert / update into tables. — Vikdor
– Vikdor, Commented Oct 22, 2012 at 5:32
Sorry, i don't have this type of option.I have to do this, using stored procedure. — riad
– riad, Commented Oct 22, 2012 at 5:37

roman · Accepted Answer · 2012-10-22 05:56:36Z

well I hate to do such a things on SQL Server, but

declare @FieldName nvarchar(max) = '[a],[b],[c],[d]'
declare @values nvarchar(max) = '5,6,7,8'
declare @i int, @j int, @break int
declare @a nvarchar(max), @b nvarchar(max), @result nvarchar(max)

select @break = 0

while @break = 0
begin
    select @i = charindex(',', @FieldName), @j = charindex(',', @values)

    if @i > 0 and @j > 0
    begin
        select @a = left(@FieldName, @i - 1), @b = left(@values, @j - 1)

        select @FieldName = right(@FieldName, len(@FieldName) - @i), @values = right(@values, len(@values) - @j)
    end
    else
    begin
        select @a = @FieldName, @b = @values, @break = 1
    end

    if @a in ('[b]', '[d]')
        select @result = isnull(@result + ' & ', '') + @a + '=' + @b

end

select @result

You can also put all this list into temporary/variable table and do join.

select *
from
(
    select T.<yourcolumn>, row_number() over (order by T.<yourcolumn>) as rownum
    from <temptable1> as T
) as F
    inner join
    (
        select T.<yourcolumn>, row_number() over (order by T.<yourcolumn>) as rownum
        from <temptable2> as T
    ) as V on V.rownum = F.rownum

Or, even better, you can pass parameters into sp in xml form and not in distinct lists

praveen · Accepted Answer · 2012-10-22 06:06:13Z

Try this :
Using XML i'm are trying to spilt the values and storing the result in a table variable

    DECLARE @FieldName VARCHAR(MAX),
    @values varchar(max)

    SET @FieldName = 'a,b,c,d';
    SET @values = '5,6,7,8'

    SET @FieldName = @FieldName + ',';
    SET @values = @values + ',';

    DECLARE @X XML
    SET @X = CAST('<Item>' + REPLACE(@FieldName, ',', '</Item><Item>') + '</Item>' AS XML)

    Declare @X1 XML
    Set @X1=CAST('<Position>' + REPLACE(@values, ',', '</Position><Position>') + '</Position>' AS XML)

    Declare @FieldSample table
    (
     FieldName char(1),
     rowNum int
    )

    Declare @valueSample table
    (position int,
     rowNum int)

    Insert into @FieldSample(rowNum,FieldName)
    Select * from  (
    SELECT row_number() over (order by (select 0)) as rowNum, t.value('.', 'char(1)')   as field
    FROM @x.nodes('/Item') as x(t)
    ) as a
   where a.field !=''

   Insert into @valueSample(rowNum,position)
   Select * from (
   Select row_number() over (order by (select 0)) as rowNum, k.value('.', 'int') as position
   from @X1.nodes('/Position') as x1(k) 
   ) as b
   where b.position !=0

Basically the last logic you can change it based on how you intend to get the data

  Select a.FieldName,b.position from @FieldSample as a
  inner join @valueSample as b
  on a.rowNum=b.rowNum
  where b.position = 6 or b.position =8

Collectives™ on Stack Overflow

Retrieve multiple pieces of data from a single SQL Server variable

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