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

Question

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'

The best way to do this is to avoid cursors altogether. 99% of the time you really don't need a cursor.... — marc_s
– marc_s, Commented Oct 19, 2016 at 15:07
Since you are new to cursors you should use a set based and continue to be inexperienced with cursors. They are the work of the devil. — Sean Lange
– Sean Lange, Commented Oct 19, 2016 at 15:08
Input is @str in the given code and output are @-str1 and @-str2 those I'm inserting into table — Somashekhar
– Somashekhar, Commented Oct 19, 2016 at 15:21

John Cappelletti · Accepted Answer · 2016-10-19 17:10:38Z

1

OK, Assuming #Temp looks something like this:

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

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',' ')

answered Oct 19, 2016 at 17:10

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

4 Comments

Somashekhar Over a year ago

Thanks a lot john. I was looking for the same. You always help me :)

John Cappelletti Over a year ago

@SomashekharKendule Happy to help. Cheers.

Hogan Over a year ago

nice answer. look, no cursor!

John Cappelletti Over a year ago

@Hogan High praise from someone of your stature. I'd sooner stick a needle in my eye than use a cursor.

Hogan · Accepted Answer · 2016-10-19 15:40:45Z

0

I think what you are trying to do is find the 6th element in a string deliminated by |.

If that is the case you should not use a loop (as is always the case in SQL) instead in SQL Server you can use the STRING_SPLIT function (https://msdn.microsoft.com/en-us/library/mt684588.aspx) like this:

SELECT value  
FROM STRING_SPLIT(@sql, ' | ')  
OFFSET ROWS 5 FETCH FIRST 1 ROW ONLY

answered Oct 19, 2016 at 15:40

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

3 Comments

Somashekhar Over a year ago

STRING_SPLIT is not builtin function in SQL server 2008

Hogan Over a year ago

@SomashekharKendule - a simple google search would locate a stub prior to updating to current version.

Hogan Over a year ago

@SomashekharKendule - you will also find answers that translate the string into xml and then use the xml query functions to solve this kind of problem.

Collectives™ on Stack Overflow

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

Ouput

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Ouput

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related