Cursor issue

[AndyInUK]

SET NOCOUNT ON

DECLARE @ID uniqueidentifier,

    @vFirstName nvarchar(255),
    @vLastName nvarchar (10)

DECLARE customer CURSOR FOR

SELECT @ID,
FirstName,
LastName
FROM user

OPEN Customer

FETCH Customer INTO @ID,
@vFirstName,
@vLastName

WHILE @@Fetch_Status = 0

BEGIN

FETCH Customer INTO @ID,
@vFirstName,
@vLastName

Insert into test values (@ID,@vFirstName, @vLastName)

END

CLOSE Customer

DEALLOCATE Customer

RETURN

The above cursor works good, but what i want to change above is - Instead of taking ID from user table , i want to

SET ID = newid()

but on adding this after DECLARE customer am getting error. How can i get around this problem ? Thank You Andyyy

[Ashfield]

AndyInUK wrote:

The above cursor works good,

Are you sure? I think you lose your first record. Why select the ID in your cursor if you don't use it. Then you should be able to add your set just before the insert - or even as part of it.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[AndyInUK]

Ashfield wrote:

you should be able to add your set just before the insert

oh yeh i was trying to SET way up after DECLARE customer, it works nicely before Insert - that was school boy error :~ .

Ashfield wrote:

I think you lose your first record.

Yeah actually i am losing 1 record, can you please tell what did i do wrong in that respect.

Ashfield wrote:

Why select the ID in your cursor if you don't use it.

yeah i did removed it when i was setting ID. Thanks for your help

[Ashfield]

In your WHILE you need to put your insert before your fetch - you got your 1st record outside the loop

OPEN Customer
FETCH Customer INTO ......
WHILE @@Fetch_Status = 0
BEGIN
Insert into test values (.....)
FETCH Customer INTO ....
END

Pleased to help

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Bassam Saoud]

I like to add a note that Cursors are really bad for performance. It is very esily converted to a while loop. If you need help let me know.

[Lost User]

Cursors are slow because they process results row-by-row and a while loop does the same, how come it be faster then ?

[RyanEK]

Avoid cursors where possible. You could do this with one statement...

insert into test (id, vfirstname, vlastname)
select newid(), vFirstname, vLastName from customer

[Niladri_Biswas]

Hi, Try to understand the output of NewId() If I issue select NewId() the output will be something like

3B311450-65BB-4AF8-898B-0CD02C5086E2

. It is a pure hyphenized alphanumeric charecter. If your destination table has the Id column as integer type, then you need to either change the column type from Integer to Varchar or you need to extract only the numeric values from NewId() for your future use. I have created a sample for you. Look into that(id datatype is varchar) . I hope you will get some insight.

declare @tblSource table(id int identity, FirstName varchar(50),LastName varchar(50))
declare @tblDest table(id varchar(max) , FirstName varchar(50),LastName varchar(50))
insert into @tblSource
select 'firstname1', 'lastname1' union all
select 'firstname2', 'lastname2' union all
select 'firstname3', 'lastname3' union all
select 'firstname4', 'lastname4' union all
select 'firstname5', 'lastname5'

Query:

insert into @tblDest (id,FirstName,LastName)
select NEWID(),FirstName,LastName
from @tblSource

select * from @tblDest

Output:

id FirstName LastName
D2A3D81C-F76E-44A4-9D47-83FBE4DDB76B firstname1 lastname1
0B827C0E-EB68-43EA-A423-B10575EAF572 firstname2 lastname2
EB4DB402-3D8F-4C09-9E37-004CE37DE1FD firstname3 lastname3
E6CE9239-1E95-4660-B5AC-00DBFEE28474 firstname4 lastname4
345F70CC-B957-41CC-977C-9FBA7A81E912 firstname5 lastname5

In case you need to strip out only the numbers, here is an example

declare @str varchar(max)
set @str = 'D2A3D81C-F76E-44A4-9D47-83FBE4DDB76B'
;with cte as(
select 1 as rn
union all
select rn+1 from cte where rn<LEN(@str)),
cte2 as(
select rn,chars
from cte
cross apply(select SUBSTRING(@str,rn,1) chars)X
where chars like '%[0-9]%'
)
select numericData from (
select cast(chars as varchar(max))from cte2
for xml path(''))X(numericData)

Output:

numericData
23817644494783476

:)

Niladri Biswas

modified on Tuesday, December 8, 2009 4:12 AM