Conversion failed while converting nvarchar value to datatype int

Question

I want to get the values of @sql to p. But while conversion it says that "Conversion failed when converting the nvarchar value 'select sum(stock) from[Hard disk]' to data type int."

declare @tname varchar(10);
declare @p int
declare @i int

declare @sql nvarchar(max);

set @tname = 'Hard disk';

set @sql = 'select sum(stock) from' + '[' + @tname + ']'

exec  (@sql)

print @sql

select @p = convert(int,@sql)

print @p

What i want is to assign the result of the query @SQL in integer variable @p..

Answer 1

One way to do it is do it all in the dynamic sql.

declare @tname varchar(10);
declare @p int
declare @i int

declare @sql nvarchar(max);

set @tname = 'Hard disk';

set @sql = 
'DECLARE @Result AS INT
select @Result = sum(stock) from' + '[' + @tname + ']
PRINT @Result'

exec  (@sql)

The other way would be creating an output param.

DECLARE @tname VARCHAR(50)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Result INT

SET @tname = 'Hard disk';
SET @SQLString = N'SELECT @Result = sum(stock)
                   FROM ' + QUOTENAME( @tname )  
SET @ParmDefinition = N'@Result INT OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@Result=@Result OUTPUT

PRINT @Result

You better use QUOTENAME for embrasing the table name with the brackets, as it is more native.