Declare @count nvarchar(max)
set @count ='select COUNT(*) from '+ @tablename+''
if( @count =0 )
begin
print 'fail'
end
else
begin
print 'success'
end
end
the @count variable is not getting the value 0. it shows the error as
Conversion failed when converting the nvarchar value 'select COUNT(*) from tablename' to data type int.
Best Answer
Be extra careful with dynamic sql like this, as you open yourself up to sql injection. So make sure @tablename is sanitized.
One check to be safe would be something like this, by making sure the table exists using a parameterised query before attempting the dynamic query:
If @Count then comes out at -1, you know it's because the tablename is invalid
Edit:
Reference to sp_executesql is here