casting multiple integer values in sql

Question

Hello - I would like to cast multiple variables which are coming from .net solution to integer. Something like this: I am using IN keyword for selecting more then one keyword, because I don't know how many variables are selected by the customer and I am putting them in nvarchar variable.

Example:

StatusID selected: 30,40,50

'30,40,50' its passed as sql nvarchar parameter to the sql server

and I want to convert them to integer and have something like: IN (cast(30,40,50 as int)) This way I am getting an error.

Can someone help me how I can set the proper sql syntax. Thanks, Laziale

Answer 1

Best is to construct the sql on the client and then send it to SQL because it will be impossible to do on SQL Server side.

You can do it this way:

string parameters = "30, 40, 50";
string sqlFormat = "SELECT * FROM MY_TABLE WHERE FIELD IN({0})";
string sql = String.Format(sqlFormat, parameters );
myReader = command.ExecuteReader(sql);

UPDATE

For those friends that think this opens for SQL injection, the list of numbers are created by the application and user only selects items from the list. So this is not open to user and possible SQL injection.

Answer 2

SELECT *
FROM YourTable
WHERE StatusId IN(CAST('30' AS int),CAST('40' AS int),CAST('50' AS int))

Answer 3

You're using the .NET tag. Are you using ADO.NET? Are you setting up a text query, or are you calling a stored proc?

For plain vanilla Transact-SQL, if you have a string representing a list of values, you'll have to parse the string and convert each value to an integer. There isn't a built-in Split() function like in .NET but you do have substring and index-of functions to find and extract the numbers. Then, convert each to an integer, and store them in a temp table or table variable, that you will use in the IN clause.

For most .NET applications, you should be able to use the string as part of a string-represented query, for whatever query command parser you have available. If it's basic ADO, that'll be SQL, for NHibernate it'd be HQL, etc. Just set up a string-based query and send it to SQL Server, and it'll parse the whole thing into a command, no conversion on your part needed.

Answer 4

You can do this on sql server side but I don't like it:

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
returns @Results TABLE (Items nvarchar(4000))  
as
begin
declare @index int
declare @slice nvarchar(4000)
select @index = 1
if @String is null return
while @index != 0
begin
select @index = charindex(@Delimiter,@String)
if @index !=0
select @slice = left(@String,@index - 1)
else
select @slice = @String
insert into @Results(Items) values(@slice)
select @String = right(@String,len(@String) - @index)
if len(@String) = 0 break
end return
end

And then:

WHERE someintegerfield IN (select * from dbo.Split('30,40,50', ','))