Somashekhar Kendule Somashekhar Kendule - 1 month ago 10
SQL Question

Passing dynamic select values to cursor in SQL (cursor usage)

I'm new to cursors,

How should I write my below code in cursor to pass column value to variable @str

Basically @str in below code is taking static value, need to pass table column values

DECLARE @str VARCHAR(1000),
@str1 VARCHAR(1000),
@str2 VARCHAR(1000),
@pos INT,
@counter INT

SET @str = '45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00.............. etc'

(Here i need to pass table column value like--- select name from Order so every time it takes new value)

--select @str = temp FROM OrderTemp

SET @counter = 0
SET @pos = 0

WHILE @counter <= 6
BEGIN
SET @pos = CHARINDEX('|', @str, @pos + 1)
SET @counter = @counter + 1
END

SET @str1 = SUBSTRING(@str, 1, @pos)
SET @str2 = SUBSTRING(@str, @pos+1, LEN(@str)-@pos)

insert into OrderInterface (name)(SELECT @str2)
insert into OrderInterface (Id)(SELECT @str1)


Input

@str :-- '45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00.............. etc'

Now i need to pass Input value from table like : Select name from tablename instead of static values.


Ouput



@str1 :-- '45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg'

@str2 :-- 'TBL101 | PC | 1.00 | COMP101 | CS | 1.00.............. etc'

Answer

OK, Assuming #Temp looks something like this:

enter image description here

Then we can do the following:

;with cte as (
              Select A.RN,B.* 
               From  (Select *,RN = Row_Number() over(Order By (Select NULL)) From #Temp) A
               Cross Apply [dbo].[udf-Str-Parse](A.Temp,' | ') B
             )
-- Insert Into OrderInterface  (Name,ID)
Select Name = (Select String1=Stuff((Select  ' | ' + RetVal From cte Where RN=A.RN and RetSeq<=7 For XML Path ('')),1,3,'') )
      ,ID =   (Select String1=Stuff((Select  ' | ' + RetVal From cte Where RN=A.RN and RetSeq>7  For XML Path ('')),1,3,'') )
 From cte A
 Group By A.RN

Which Returns

enter image description here


The UDF if Needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')