CODEWITHSUNDEEP
sqlsql-servert-sqlcursordynamic-sql
rut4
rut4

Reputation: 31

Update identity column in dynamic T-SQL

I should write a SQL script in which I use a dynamic cursor. I want to reidentity table rows and has written stored procedure:

CREATE PROCEDURE [dbo].[ReidentityComments]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE Reidentitier CURSOR
        LOCAL
        SCROLL
        DYNAMIC
        FOR
            SELECT * FROM Comment
        FOR UPDATE;

    OPEN Reidentitier;

    DECLARE @CommentId INT;
    DECLARE @FilmId INT;
    DECLARE @Text NVARCHAR(2000);
    DECLARE @PlacingDate DATETIME;
    DECLARE @UserId INT;

    DECLARE @current INT;
    SET @current = 1;

    DECLARE @updateSql NVARCHAR(100);
    SET @updateSql = N'
        SET IDENTITY_INSERT VideoLibrary.dbo.Comment ON;
        UPDATE Comment SET CommentId = @cur WHERE CommentId = @id;
        SET IDENTITY_INSERT VideoLibrary.dbo.Comment OFF;
    ';

    DECLARE @params NVARCHAR(100);
    SET @params = N'@cur INT, @id INT';

    FETCH NEXT
        FROM Reidentitier
        INTO @CommentId, @FilmId, @Text, @PlacingDate, @UserId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @CommentId != @current
            EXECUTE sp_executesql @updateSql, @params, @cur = @current, @id = @CommentId;

        FETCH NEXT
            FROM Reidentitier
            INTO @CommentId, @FilmId, @Text, @PlacingDate, @UserId;

        SET @current = @current + 1;
    END
END

But when I try to execute the procedure I get the error:

An expression of non-boolean type specified in a context where a condition is expected, near 'Co'.

Can anyone help me, please?

Upvotes: 1

Views: 897

Answers (2)

Trubs
Trubs

Reputation: 3032

You wont be able to update the identity column anyway (regardless of Identity_insert value).

So to solve your problem - Does it really need to be the identity column? (if the Identity column is also the primary key you could get into issues if you have data referring to the comments table.)

Could you just create a column called RowNum (for instance) - and re populate that using Row_Number()?

eg

UPDATE Comment SET RowNum = c.NewSequence
FROM (
      SELECT CommentId, NewSequence = ROW_NUMBER() OVER (ORDER BY CommentId)
      FROM Comments) c
WHERE c.Id = Comment.id

Upvotes: 0

Spock
Spock

Reputation: 4900

The problem in your code is in the declaration of @updateSql nvarchar(100) is too small to store the whole string. It's getting truncated, and you're ending up with something like... 

SET IDENTITY_INSERT VideoLibrary.dbo.Comment ON;
UPDATE Comment SET CommentId = @cur WHERE CommentId

And that's when the error is returned...

That said, i.m.h.o. a better approach to do what you're trying to do would probably be:

SELECT IDENTITY (int, 1, 1) AS CommentId, FilmId, Text, PlacingDate, UserId
INTO TMP_COMMENT
FROM Comment;

exec SP_RENAME Comment, OLD_COMMENT;
exec SP_RENAME TMP_COMMENT, Comment;

The only downside to this approach is that none of the indexes, or primary keys will be created on the new comment table.

Upvotes: 2

Related Questions