Conversion failed when converting varchar into INT

Question

I know there are a lot of questions asked like this, but I have tried almost all of the solution, but still can't get that one piece of code to work :( Here's what I'm doing - I'm trying to find the number of rows in a table so that I can use that as a counter to loop and delete rows.

@rc is INT, @tbname is varchar (500) and @id is also INT.

This is my query:

set @rc='select count(*) from dbo.[' + @tbname + '] where id = ' + @id

I have also tried these:

set @rc='select cast(count(*) as varchar) from dbo.[' + @tbname + '] where id = ' + @id

and

set @rc='select count(*) from dbo.[' + @tbname + '] where id = ' + cast(@id as varchar)

And a few more permutation of placing cast here and there as well. I also tried changing the declaration of @rc as varchar, still get the same error.

You expect that merely *assigning* a *string* to a variable should cause the string to be considered as a query and *executed*? That would make for a remarkably surprising language. — Damien_The_Unbeliever, May 02 '17 at 09:08
This is the method you need to use. http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable Try it and post back any questions — Nick.McDermaid, May 02 '17 at 09:09
when asking a question please post all the code. This includes the `print`, and the `declare`. Anyway - go check the link I posted — Nick.McDermaid, May 02 '17 at 09:11
@Balaji `@rc` is a string. The execution of this string produces a scalar result that will be `INT`. But the `rc` itself is a string/varchar. — Ivan Sivak, May 02 '17 at 09:14

SHD · Accepted Answer · 2017-05-02T09:59:59.703

1

Use sp_executesql in this case,

    DECLARE @retval int   
    DECLARE @sSQL nvarchar(500);
    DECLARE @ParmDefinition nvarchar(500);

   DECLARE @rc          INT
            ,@str       NVARCHAR(MAX)
            ,@tbname    NVARCHAR(500) = 'Table1'
            ,@id        int

    set @str='select @rcOut = count(*) from dbo.[' + @tbname + '] where id = ' + CAST(@id as NVARCHAR)


    EXEC sp_executesql @str, N'@rcOut int OUTPUT',@rcOut = @rc OUTPUT;

    SELECT @rc;

edited May 02 '17 at 09:59

answered May 02 '17 at 09:53

SHD

399
3
12

Thank you. There was another answer an hour ago, something similar to this, I was able to use that. Not sure why that was deleted. – mathB May 02 '17 at 10:34
1

This is the correct method in this scenario, u dont need to create temp. table. – SHD May 02 '17 at 10:45

Conversion failed when converting varchar into INT

1 Answers1