Conversion error when converting nvarchar value to int

Question

Declare @count nvarchar (max)

set @count ='select COUNT(*) from '+ @tablename+''

if( @count =0 )
begin 
  print 'fail'
end
else
begin
  print 'success'
end
end

the variable @count does not get the value 0. It shows the error as

Conversion error when converting nvarchar 'value select COUNT (*) from tablename' to int data type.

Answer 1

This should work:

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @count int

SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM ' + @tablename;
SET @ParmDefinition = N'@CountOUT int OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @CountOUT=@count OUTPUT;

SELECT @count;

Answer 2

DECLARE @Count INTEGER
DECLARE @nSQL NVARCHAR(1000)
SET @nSQL = 'SELECT @Count = COUNT(*) FROM ' + @tablename
EXECUTE sp_executesql @nSQL, N'@Count INTEGER OUT', @Count OUT

-- Now check @Count

Be careful with dynamic sql like this when you open yourself before SQL injection. So make sure @tablename is cleared.

One check to be safe would be like this, making sure the table exists using a parameterized query before attempting a dynamic query:

DECLARE @Count INTEGER
DECLARE @nSQL NVARCHAR(1000)
SET @nSQL = 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName) 
    SELECT @Count = COUNT(*) FROM ' + @tablename + '
ELSE
    SELECT @Count = -1'

EXECUTE sp_executesql @nSQL, N'@TableName NVARCHAR(128), @Count INTEGER OUT', @TableName, @Count OUT

@Count -1, , _

Edit:
sp_executesql

Answer 3

Erland SQL.

Answer 4

@count int:

DECLARE @count AS INT
SELECT @count = COUNT(*) FROM YourTable

,

SELECT @countAsString = 'SELECT ...'

@countAsString ( ) , .

EXEC .