Conversion failed when converting the varchar value ' WHILE (' to data type int

Question

I have the below transaction:

DECLARE @strsql1 NVARCHAR(MAX);
DECLARE @rows INT, @count INT;
DECLARE @MySource NVARCHAR(30);
DECLARE @Myfield NVARCHAR(30);

SET @rows = 1;
SET @count = 0;

SELECT @strsql1 = 
'WHILE ('+@rows+')> 0
BEGIN
BEGIN TRAN
delete top (10000)  from '+@MySource+'
where' +@Myfield+' =''value''
SET '+@rows+' ='+ @@ROWCOUNT+'
SET '+ @count+'  = '+ @count+ @rows +' 
RAISERROR(''COUNT %d'', 0, 1, '+ @count+') WITH NOWAIT
COMMIT TRAN
END;'

PRINT @strsql1
EXEC sp_executeSql @strSql1

but I get this error message:

Conversion failed when converting the varchar value ' WHILE (' to data type int.

I have try to use the cast with the two variables (@count and @row) but the problem reside.

Could you please suggest some solution?

Thank you.

Why are you using dynamic here in the first place? It is a level of complexity you don't need. — Sean Lange, Sep 12 '19 at 15:23
Exactly what I was thinking, @SeanLange. There might be some confusion by thinking that variables need to use dynamic sql, otherwise, the code will try to assign values to a literal. — Luis Cazares, Sep 12 '19 at 15:27
MySource is a variable too? That wasn't clear. You are going to have to rewrite almost all of this because your variables have be defined **inside** the dynamic sql. Things like @rows and such. — Sean Lange, Sep 12 '19 at 15:40
Yes i know but i have just present the problem, now the post has been edited you can see — Fatiso, Sep 12 '19 at 15:44
Why do you need to parameterize the table name for doing batch deletes? This screams of a poor design. — Sean Lange, Sep 12 '19 at 16:18
cause i use this in a stored procedure and the table is given as parameter — Fatiso, Sep 12 '19 at 16:20
Right...a stored procedure for batch deletes on an table screams of poor design. — Sean Lange, Sep 12 '19 at 16:27
what do you suggeste? we have to split the instruction delete and for this we use the batch — Fatiso, Sep 12 '19 at 16:31

pmbAustin · Answer 1 · 2019-09-12T16:42:42.930

First thing, when adding the value of an integer variable to a string, you should CAST it, for example:

SELECT @strsql1 = 'WHILE (' + CAST(@rows AS varchar) +') > 0'

However, you don't REALLY want to do that either... because you want to test the variable against the constant zero, it should be more like:

SELECT @strsql1 = 'WHILE (@rows > 0)'

But this line right here is just completely wrong... the result would be SET 2 = 3 or something like that, which makes no sense:

SET ' + @rows + ' =' + @@ROWCOUNT + '

So you have a LOT of problems here.

They key to dynamic SQL is not rushing ahead and executing it... try just PRINTING the string first, and look at the result, and see what's wrong with that (it should be obvious... copy/paste it into a new SSMS query window and you can see right away what's wrong), and then iterate, fixing your dynamic SQL generation until the generated SQL is correct. THEN execute it.

Basically, you need to leave the variable alone in the dynamic SQL, and then pass the variable in via the optional arguments to sp_executesql. There are many examples on-line of how to pass arguments in. But the declarations of @rows should probably go inside your dynamic SQL as well. Remember that dynamic sql executes in its OWN context, not the context of the calling code, so it can't see any variables declared outside. And when building dynamic SQL, you want to only use the things that will change PER CALL outside the string.

Yes i have do the Printing to see exactly the query. Thank you — Fatiso, Sep 12 '19 at 16:38
I have edited my response to be a bit more clear about the general concept that is wrong in your code. You're really going to have to go back and re-think I think. Read up on declaring and passing variables/parameters to dynamic SQL, and understand that if the resulting SQL you PRINT out can't run all by itself in its own SSMS Query window, it won't work when you try to call sp_executesql with it — pmbAustin, Sep 12 '19 at 16:43

score 0 · Answer 2 · answered Sep 12 '19 at 18:26

Maybe something like this will get you started.

declare @MySource sysname = 'YourTableName'
    , @MyField sysname = 'YourFieldName'
    , @strsql1 NVARCHAR(MAX)

SELECT @strsql1 = 
'DECLARE @rows INT = 1;
WHILE @rows > 0
BEGIN
    delete top (10000)  from ' + QUOTENAME(@MySource)
    + ' where ' + QuoteName(@Myfield) + '=''value'';
    SET @rows = @@ROWCOUNT;
    print convert(varchar(10), @rows) + '' row(s) affected'';
END;'

PRINT @strsql1
--EXEC sp_executeSql @strSql1

Thank you Sean, i am triying to test the query, the need is to execute the delete statement on 20 000 000 of records :/ — Fatiso, Sep 13 '19 at 10:45

Conversion failed when converting the varchar value ' WHILE (' to data type int

2 Answers2