CODEWITHSUNDEEP
sql-serverdatabaset-sqlssms-2014
YanaSh
YanaSh

Reputation: 3

Cursor in T-SQL doesn't work

I am using cursor to randomly copy data from one table(Family_tree) to two other tables(Family_tree1 + @time, Family_tree2 + @time). Code executes successfully, but no row is updated. There IS actually what to copy from a table. I am using Microsoft SQL Server Management Studio. Here's the part of script with cursor:

---creating two tables beforehand
DECLARE @random int
DECLARE 
@first_name nvarchar(20),
@last_name AS nvarchar(20),
@date_of_birth AS nchar(10),
@date_of_death AS nchar(10),
@place_of_death AS nvarchar(30),
@credit_card AS nchar(16),
@ID_member AS int,
@FK_gender AS nchar(3), 

DECLARE curs CURSOR FOR SELECT first_name, last_name, date_of_birth, date_of_death, place_of_death, credit_card, ID_member, FK_gender,  FROM Family_tree
OPEN curs

FETCH NEXT FROM curs INTO  @first_name, @last_name, @date_of_birth, @date_of_death, @place_of_death, @credit_card, @ID_member, @FK_gender, 
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @random = RAND() * 1000000
    IF @random % 2 = 1
    BEGIN 
        SET @sqlString = 'INSERT INTO [Family_tree1' + @time +  ']  (first_name, last_name, date_of_birth, date_of_death, place_of_death,  credit_card, ID_member, FK_gender) 
    VALUES (' 
     + @first_name  + ','  + @last_name  + ',' + @date_of_birth + ',' + @date_of_death + ',' + @place_of_death + ',' + @credit_card + ',' 
     + CAST(@ID_member AS nvarchar)  +','+ @FK_gender  + ')'
    END
    ELSE
    BEGIN
    SET @sqlString = 'INSERT INTO [Family_tree2' + @time +  '] (first_name, last_name, date_of_birth, date_of_death, place_of_death, credit_card, ID_member, FK_gender) 
    VALUES (' + @first_name  + ','  + @last_name  + ',' + @date_of_birth + ',' + @date_of_death + ',' + @place_of_death + ',' + @credit_card + ',' 
     + CAST(@ID_member AS nvarchar)  +','+ @FK_gender + ')'
END
EXECUTE(@sqlString)
FETCH NEXT FROM curs INTO  @first_name, @last_name, @date_of_birth, @date_of_death, @place_of_death, @credit_card, @ID_member, @FK_gender
END
CLOSE curs
DEALLOCATE curs
END;

I am new to T-SQL and will appreciate any advice! Thank You in advance (:

Upvotes: 0

Views: 478

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

If you "have" to do it in the cursor, then you need to watch out for nulls. You also need to watch out for the fact that you're using strings and these should be quoted when inserted into a VALUES clause.

Instead of

VALUES (' + @first_name  + ',

You need something like:

VALUES (' + COALESCE('''' + REPLACE(@first_name,'''','''''') + '''','NULL')  + ',

And so on for the rest of your values. This replaces any single quotes within the value with doubled-up quotes, then wraps the whole string in single quotes. NULLs survive through all of that processing so we then also use COALESCE to replace the NULL with a NULL literal in the eventual string1.

Before running the cursor in anger, I'd suggest you do this for one row and print the string rather than executing it, to check that it "looks right".

I'd also suggest you look into using better data types - dates of birth/death would be much better dealt with as actual date values rather than strings.

1Guido suggested ISNULL in the comments, which is similar to COALESCE but has some odd limitations and I'd usually recommend against using. They also suggested that the replacement should be an empty string, but here that would result in VALUES(... ,, ...) at the position of the NULL value which would generate an error.

Upvotes: 1

Related Questions