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.
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))+ ''
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)
? !!