Pfthb

Pardon me, I was trying hard to find the answer, but most of the questions are related in the forum related to converting one value, not the whole set.

I am trying to pass the subquery values to the main query but the subquery returning varchar and the main query column is accepting smallint. I tried cast and convert but didn't help me.

select time_off_type_no 
from schedtime 
where activity_no in (select AT_NUMBERS from ACTIVITY where AT_ID = 105)

This query is throwing the following exception

Conversion failed when converting the varchar value '483,484,485,486,487,488,489' to data type smallint

Any advice on how to convert the values much appreciated.

Following query returning '483,484,485,486,487,488,489' and I want to convert all the values to SmallInt or int to pass it to the main query.

select AT_NUMBERS 
from ACTIVITY 
where AT_ID = 105

Please try nested casting like:

SELECT CAST(CAST(AT_NUMBERS AS DECIMAL) AS SMALLINT) from ACTIVITY where AT_ID=105

EDIT: Since the returned value is a comma-delimited string, I think this would help if the version of SQL Server is at least 2016

;with cte (ID) as (
 Select string_split (AT_NUMBERS,',') as ID
 from ACTIVITY
 where AT_ID=105
)
select time_off_type_no from schedtime where activity_no in (
 SELECT CAST(CAST(ID AS DECIMAL) AS SMALLINT) from cte
)

If SQL SERVER version is below 2016, we'll need to develop our own split function. You can find examples in How to split a comma-separated value to columns

Try this as an example if so, working in Sql Server 2008:

DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
)
INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')


SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Ref: https://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/

I think you need to split the string by comma if these (483,484,485,486,487,488,489) are individual numbers. If this is whole integer value, not even Big Int limit is like this.

See MS documentation:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

If your SQL server version is more than 2016 or more, then you can use string_split function in this way.

--Use try_cast or Try_convert to avoid any conversion error as well.

select Try_cast(value as int) Integervalue from string_split ('483,484,485,486,487,488,489',',') 

Output:

Integervalue
483
484
485
486
487
488
489

--this will work if it is pure integer value, else it needs to be converted to decimal.

Please make sure to use cross apply if you are using against tables.

If it is less than 2016, you might have to build one string split function as mentioned here.

Splitting the string in sql server