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..

possible duplicate of [Getting result of dynamic SQL into a variable](http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable) — cha, Sep 19 '13 at 06:56

Giannis Paraskevopoulos · Accepted Answer · 2013-09-19T07:07:50.883

0

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.

edited Sep 19 '13 at 07:07

answered Sep 19 '13 at 06:56

Giannis Paraskevopoulos

18,261
1
49
69

I can't use the first one as I need to perform some integer operations on the value we get. But the second one looks cool. It would be great if you could also explain me 2nd one in brief.. – Jack Stern Sep 19 '13 at 08:49
It is a standard way of using dynamic sql that returns one or more values. In the ParamDefinition you may define more variables input or output separated by commas. For the input variables you do not have to mark them as input. You may see more details at http://technet.microsoft.com/en-us/library/ms188001.aspx – Giannis Paraskevopoulos Sep 19 '13 at 09:12

score 0 · Answer 2 · answered Sep 19 '13 at 07:08

0

declare @p int;
EXEC SP_EXECUTESQL N'select @p = sum(stock) from '[' + @tname + ']',N'@p int OUTPUT',@p    OUTPUT;
select @p

answered Sep 19 '13 at 07:08

GBoehm

183
1
16

Conversion failed while converting nvarchar value to datatype int

2 Answers2