SQLTeam.com | Weblogs | Forums

Conversion failed when converting the nvarchar value


#1

I am getting the conversion failed when converting the nvarchar value, can you please tell me any formatting with in select is wrong:
Thanks a lot for the helpful info.

eclare @NoOfItems as int
declare @StartOffSet as int
declare @searchKey as varchar(50)
DECLARE @UserID INT

set @searchKey = 'd'
set @StartOffSet = 0

set @NoOfItems = 10

set @query=N'Select 0 as ProgID, ''* Select *'' as ProgNO,null as ProgName, null as AccessMode union SELECT A.ProgID, B.ProgNO, B.ProgName, A.AccessMode FROM TAB_ccsNetUserPrograms AS A JOIN

					TAB_ccsNetPrograms AS B ON A.ProgID = B.ProgID

				WHERE  A.UserID = @UserID AND A.AccessMode = 2  AND  A.Deleted = ''0'' and progno like ''%'+@searchKey+'%''order by progno OFFSET '+@StartOffSet+' ROWS FETCH Next '+@NoOfItems+''

---I am getting this error:
Conversion failed when converting the nvarchar value 'Select 0 as ProgID, '* Select *' as ProgNO,null as ProgName, null as AccessMode union SELECT A.ProgID, B.ProgNO, B.ProgName, A.AccessMode FROM TAB_ccsNetUserPrograms AS A JOIN

					TAB_ccsNetPrograms AS B ON A.ProgID = B.ProgID

				WHERE  A.UserID = @UserID AND A.AccessMode = 2  AND  A.Deleted = '0' and progno like '%D%'order by progno OFFSET ' to data type int.

Discourse Annoyances
#2

You should cast the INT variables to character type before concatenating, e.g. like shown below:

DECLARE @NoOfItems AS INT
DECLARE @StartOffSet AS INT
DECLARE @searchKey AS VARCHAR(50)
DECLARE @UserID INT = 12345;

SET @searchKey = 'd'
SET @StartOffSet = 0

SET @NoOfItems = 10
DECLARE @query NVARCHAR(4000);

SET @query = N'Select 0 as ProgID, ''* Select *'' as ProgNO,null as ProgName, null as AccessMode union 
SELECT A.ProgID, B.ProgNO, B.ProgName, A.AccessMode FROM TAB_ccsNetUserPrograms AS A JOIN

						TAB_ccsNetPrograms AS B ON A.ProgID = B.ProgID

				WHERE  A.UserID = ' + CAST(@UserID  AS VARCHAR(32)) + ' AND A.AccessMode = 2  AND  A.Deleted = ''0'' and progno like ''%'
    + @searchKey + '%''order by progno OFFSET ' + CAST(@StartOffSet AS VARCHAR(32))
    + ' ROWS FETCH Next ' + CAST(@NoOfItems  AS VARCHAR(32))+ ''

#3

I agree that the @StartOffSet and @NoOfItems need CASTING to VARCHAR as this query stands, but it also includes "A.UserID = @UserID" which you have also changed to

I suspect you (and I) think that the O/P overlooked substituting that ... but actually I think it would be much better not to substitute ANY of them and use a Parametrised Query instead

DECLARE @NoOfItems AS INT
DECLARE @StartOffSet AS INT
DECLARE @searchKey AS VARCHAR(50)
DECLARE @UserID INT

SET @searchKey = 'd'
SET @StartOffSet = 0
SET @NoOfItems = 10
SET @query = 
N'Select 0 as ProgID
	, ''* Select *'' as ProgNO
	, null as ProgName
	, null as AccessMode
union
SELECT	A.ProgID
	, B.ProgNO
	, B.ProgName
	, A.AccessMode
FROM	TAB_ccsNetUserPrograms AS A
	JOIN TAB_ccsNetPrograms AS B
		 ON A.ProgID = B.ProgID
WHERE 	    A.UserID = @UserID
	AND A.AccessMode = 2
	AND  A.Deleted = ''0''
	and progno like ''%'' + @searchKey + ''%''
order by progno OFFSET @StartOffSet  ROWS FETCH Next @NoOfItems'

and then use sp_ExecuteSQL to execute it (instead of EXEC)

EXEC sp_ExecuteSQL @query
	, N'@UserID int, @searchKey varchar(50), @StartOffSet int, @NoOfItems int'
	, @UserID = @UserID
	, @searchKey = @searchKey
	, @StartOffSet = @StartOffSet
	, @NoOfItems = @NoOfItems

because:

  1. It will perform better
  2. No problem with Data Type Conversion - e.g. INT to VARCHAR but things like DATES would be much more problematic
  3. No problem with syntax errors - e.g. if @searchKey includes the ' character
  4. No issue with SQL Injection (same as 3 really, but worth re-iterating)

#4

I completely agree!


#5

Can I get you a :chug: ? !!