convert string into int SQL Server

This is the scenario:

My app will have the following:

  1. A listbox (The checkbox property enabled) that will display a list of Something.
  2. The user will select from the listbox (multiselect) by using the checkbox.
  3. I will loop into All the checked items and store the ID's into an array. I will store the ID's into something like this separating the ID with a comma (1,2,3,4) and then I will use length -1 to delete the last comma.

How can I convert the string 1,2,3,4 into an integer type of data if my stored procedure is like this?

Select * from tblSomething Where ID in (1,2,3,4)

Answers


You can use the following SQL function.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CommaSeparatedToString]
(
   @psCSString VARCHAR(8000)
)

RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
DECLARE @sTemp VARCHAR(50)
WHILE LEN(@psCSString) > 0
BEGIN
SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                LEN(@psCSString)))
SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                           LEN(@psCSString)) + 1, LEN(@psCSString))
INSERT INTO @otTemp VALUES (@sTemp)
END
RETURN
END

And call in your stored procedure like

Select * from tblSomething 
Where ID in (SELECT * FROM CommaSeparatedToString('1,2,3,4'))

Need Your Help

Why cannot System.IO.StreamReader read from my custom stream?

.net streamreader

I'm in the process of creating a custom stream for an API endpoint in my app. The stream needs to have custom logic that I don't want to get into, but suffice to say I can't use a built-in stream c...

Which Monad do I need?

haskell monads

This is something of an extension to this question:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.