Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Convert function help

Experts,

Here is my code

declare @ItemIDList varchar(8000)

set @ItemIDList = '274686,275521,275522'

select * From MyDb..MyTable where ItemID in (convert(int,@ItemIDList ))

Getting the following error.
Conversion failed when converting the varchar value '274686,275521,275522' to data type int.

Could you please help me correct if I am missing something.

Thanks in Advance.
0
Tpaul_10
Asked:
Tpaul_10
  • 4
  • 3
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Paul,

'274686,275521,275522' is not an integer.  What are you trying to do?


Kent
0
 
Tpaul_10Author Commented:
Basically I have a list of ItemIDs with comma delimited and based on the ItemIDs I need to get some information for the ItemIDs from my database table and display some information.

Hope this clarifies and please let me know if you need any further information.

Thanks
0
 
Tpaul_10Author Commented:
I have found a different way

create table #TmpItem (TmpItemID int)

set @startIndex = 1
   set @charIndex = charindex(',', @ItemIDList, @startIndex)
   if @charIndex = 0 and len(@ItemIDList) > 0
      set @charIndex = len(@ItemIDList) + 1
   while(@charIndex <> 0)
      begin
         insert into #TmpItem (TmpItemID)
         values (substring(@ItemIDList, @startIndex, @charIndex - @startIndex))

         set @startIndex = @charIndex + 1
         set @charIndex = charindex(',', @ItemIDList, @startIndex)
         if @charIndex = 0 and @startIndex < (len(@ItemIDList) + 1)
            set @charIndex = len(@ItemIDList) + 1
      end

select * From MyDb..MyTable where ItemID in (select TmpItemID from #TmpItem)

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Tpaul_10Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Tpaul_10's comment #a39749175

for the following reason:

A
0
 
Surendra NathTechnology LeadCommented:
In order to get this done you need to follow the below steps

Step 1: Create the below split function in your database

CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(2000),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 DECLARE @index INT
 SET @index = Charindex(@SplitOn,@RowData)
 While (@index>0)
 Begin
 Insert Into @RtnValue (data)
  Select
 Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))
 
 Set @RowData = Substring(@RowData,@index+1,len(@RowData))
 Set @Cnt = @Cnt + 1
 SET @index = Charindex(@SplitOn,@RowData)
 End
 
 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))
 
 Return
END

Open in new window


Step 2: re-write your query as below

select * From MyDb..MyTable where ItemID in ( SELECT CAST(DATA as BIGINT) FROM dbo.split(@ItemIDList) )

Open in new window

0
 
Surendra NathTechnology LeadCommented:
There is no proper reason why this guy wants to close this question, although now we provided him a better solution.

Can you please elaborate rather than simply typing A
0
 
Tpaul_10Author Commented:
As I have specified I found a way to get through this issue and I have posted my code as well and your comment came in after that.

I have tested my code and is working as I am expected and that was the reason I wanted to close it.

Thanks and sorry if there is any confusion
0
 
Surendra NathTechnology LeadCommented:
Thats Ok, no problem O.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now