0

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

Improve this question

4 Answers 4

Reset to default
1

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', ','))
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

2 Comments

bad idea. it is unsafe against sql-injections.
Why!? the parameters are constructed by the application, do you mean the application will SQL inject itself?
0

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.

Improve this answer

1 Comment

I am using stored procedure, here is the content:SELECT OrderId, OrderTotal, BillingCountry, OrderStatus.Name AS OrderStatus, PaymentStatus FROM DailyOrders INNER JOIN DailyOrderStatus ON DailyOrders.OrderStatusID = DailyOrderStatus.OrderStatusID INNER JOIN DailyPaymentStatus ON DailyOrders.PaymentStatusID = DailyPaymentStatus.PaymentStatusID WHERE Date > CAST(@date1 as datetime) AND Date < CAST(@Date2 as datetime) AND DailyOrders.OrderStatusID IN (@OrderStatusID)
0 
SELECT *
FROM YourTable
WHERE StatusId IN(CAST('30' AS int),CAST('40' AS int),CAST('50' AS int))
Improve this answer

1 Comment

That's all good, but I don't know how many parameters they will select at the website. Is there a way to add parameters on fly in the stored procedure or not really?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.