Dynamic SQL Error Converting Nvarchar to Int

Dynamic SQL error converting nvarchar to int

You need to CAST all numbers to nvarchar in the concatenation.

There is no implicit VBA style conversion to string. In SQL Server data type precedence means ints are higher then nvarchar: so the whole string is trying to be CAST to int.

SET @SQL =  'SELECT ' + @GName + ' AS GrName ,' + @BR
              + CAST(@T_ID AS nvarchar(10)) + ' AS To_ID ,' ...

Edit: Will A has a good point: watch for NULLs!

Error converting nvarchar to int inside a dynamic ORDER BY

CASE is an expression that returns a single value of a specific data type. The potential values must be compatible, and since you must have a name value of foo, which can't be converted to INT, you get this error. The easiest way to solve this problem is to separate your ORDER BY expressions by data type:

ORDER BY CASE @SortOrder WHEN 'id' THEN id END,
         CASE @SortOrder WHEN 'name' THEN name END;

Another alternative (which may be better for plan caching as long as you have optimize for ad hoc workloads on) would be dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... ORDER BY ' + QUOTENAME(@SortOrder) + ';';

EXEC sp_executesql @sql;

Also not sure why you would give your @SortOrder a max data type - no column name could ever be anywhere near that large.

Dynamic SQL Not Converting VARCHAR To INT (shouldn't anyway)

You need to convert your @Start to a varchar.

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'INSERT INTO TableRowCount (IntFieldID, DecimalField)
SELECT ' + CAST(@start as nvarchar(20)) +', COUNT(*)
FROM ' + @conn

SQL Server implicitly converts between datatypes on concatenation or addition based on some fairly complex criteria. Suffice to say if you try to combine an int and a string it will always attempt to convert the string to an int unless you tell it otherwise explicitly.

Below is a conversion chart for your reference from MSDN.

How to solve error Conversion failed when converting the nvarchar value to data type integer?

It appears you do not need dynamic SQL at all here.

It's hard to know what logic you are trying to achieve, owing to a lack of sample data and expected output, and your existing query could do with better formatting. But it looks like it's some kind of relational division.

Note that you should not use WITH (NOLOCK) unless you really know what you are doing. It can give wildly incorrect results.

I also strongly suggest you use table references on each column, especially within subqueries

INSERT INTO dbo.TAllData
    (PartID, Code, CodeTypeID, RevisionID, ZPLID, ConCount)
SELECT
  PartID,
  Code,
  Co.CodeTypeID,
  Co.RevisionID,
  Co.ZPLID,
  Count(1) as ConCount
FROM dbo.TPartAttributes PM
INNER JOIN dbo.TCondition Co ON Co.ZfeatureKey = PM.ZfeatureKey
Where EXISTS (SELECT 1
    FROM dbo.TAllfeatures AL
    WHERE AL.ZfeatureKey = Co.ZfeatureKey
      AND AL.IsNumericValues = 0
      AND IIF(Co.ZfeatureType = 'Qualifications', AcceptedValuesOption_Value, Name) = EStrat
)
Group By
  PartID,
  Code,
  Co.CodeTypeID,
  Co.RevisionID,
  Co.ZPLID
Having Count(1) >= (SELECT COUNT(1) FROM TCondition);

Error converting data type nvarchar to int in multiple Pivot on same column SQL

Try this

Select * FROM  
    (
    SELECT 
    [team], 
    Count_O,
    Count_Of_OA,
    Avg,
    Convert(varchar(max),[month])+ '_a'  as month_a, 
    Convert(varchar(max),[month]) + '_b' as month_b,
    [users]  
    FROM [#Temp]
    ) AS X 

PIVOT  
(  
    MAX(Count_OA)
    FOR [month_a] IN ([4_a], [5_a], [6_b])  
)   AS PivotTable1

PIVOT  
(  
    MAX(Count_O)
    FOR [month_b] IN ([4_b], [5_b], [6_b])  
)   AS PivotTable2

Error converting data type nvarchar to float with dynamic T-SQL

Declare @sMD and @sIQ as NVARCHAR as well. You're getting conversion error because you're trying to add them to varchar variables while building your @SQL statement.

---

Related Topics