I've got a saved procedure which utilizes the IN clause. During my ASP.Internet application, I've got a multiline textbox that supplies values towards the saved procedure. I wish to have the ability to order through the values because they were joined within the textbox; I discovered how to get this done easily in mySQL (using Area function), although not a SQL Server equivalent.

So my query appears like:

Select * from myTable where item in @item

And So I could be passing in values from the application like '113113','112112','114114' (within an arbitrary order). I wish to order the outcomes with that list.

Would a Situation statement be achievable? I would not know the number of products are arriving the textbox data.

How's it going parameterising the IN clause?

When you are on SQL Server 2008 I'd pass inside a Table Valued Parameter with two posts item and sort_order and join on that rather. You'll be able to just add an ORDER BY sort_order to the finish.

From KM's comment above...

I understand you did not condition it's comma seperated, but when it had been a CSV or even when it is space seperated you could do this the next.

DECLARE @SomeTest varchar(100) --used to hold your values
SET @SomeTest = (SELECT '68,72,103') --just some test data

    LoginID --change to your column names
    Login   --change to your source table name
    FROM fn_IntegerInList(@SomeTest)
) n
    n.InListID = Login.LoginID

After which create fn_IntegerInList():

CREATE FUNCTION [dbo].[fn_IntegerInList] (@InListString ntext)
RETURNS @tblINList TABLE (InListID int, SortOrder int)
declare @length int
declare @startpos int
declare @ctr int
declare @val nvarchar(50)
declare @subs nvarchar(50)
declare @sort int

set @sort=1
set @startpos = 1
set @ctr = 1
select @length = datalength(@InListString)

while (@ctr <= @length)
   select  @val = substring(@InListString,@ctr,1)

   if @val = N',' 
        select @subs = substring(@InListString,@startpos,@ctr-@startpos)
        insert into @tblINList values (@subs, @sort)
        set @startpos = @ctr+1
  if @ctr = @length 
        select @subs = substring(@InListString,@startpos,@ctr-@startpos)
        insert into @tblINList values (@subs, @sort)
  set @ctr = @ctr +1
  set @sort = @sort + 1

By doing this your function produces a table that holds a form order namely, SortOrder and also the ID or number you're passing in. You are able to obviously modify this to ensure that you're searching for space instead of , values. Otherwise Martin has got the right idea in the answer. Please be aware during my example I'm one of my tables, so you will have to alter the title Login to anything you coping.

exactly the same way you concatenate ('113113','112112','114114') to pass through towards the sql sentence within the where clausule you are able to concatenate

case item    
when '113113' then 1
when '112112' then 2
when '114114' then 3

to pass through for your order by clausule