CODEWITHSUNDEEP
sql-server
mathB
mathB

Reputation: 634

Conversion failed when converting varchar into INT

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.

Upvotes: 0

Views: 157

Answers (1)

SHD
SHD

Reputation: 409

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;

Upvotes: 1

Related Questions