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

Question

I have this query:

DECLARE @selectsql nvarchar(4000),
DECLARE @cnt int

select @selectsql = ' SELECT ' + @cnt + '= COUNT(*) FROM Vwbckup' 

print @selectsql
print @cnt

EXEC sp_executesql @selectsql

When I execute the query I'm getting this error:

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

Answer 1

Your @cnt variable is of type INT - you need to cast it to NVARCHAR to concatenate together:

DECLARE @selectsql nvarchar(4000),
DECLARE @cnt int

SELECT @selectsql = N' SELECT ' + CAST(@cnt AS NVARCHAR(10)) + N'= COUNT(*) FROM Vwbckup' 

Plus: you should prefix your string literals with N to indicate Unicode (NVARCHAR) strings

Update: that previous command really doesn't make any sense at all.... did you mean to create this command string?

SELECT @selectsql = N' SELECT @cnt = COUNT(*) FROM Vwbckup' 

and then execute it?