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
When you are on SQL Server 2008 I'd pass inside a Table Valued Parameter with two posts
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 SELECT LoginID --change to your column names FROM Login --change to your source table name INNER JOIN ( SELECT * FROM fn_IntegerInList(@SomeTest) ) n ON n.InListID = Login.LoginID ORDER BY n.SortOrder
After which create
CREATE FUNCTION [dbo].[fn_IntegerInList] (@InListString ntext) RETURNS @tblINList TABLE (InListID int, SortOrder int) AS BEGIN 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) begin select @val = substring(@InListString,@ctr,1) if @val = N',' begin select @subs = substring(@InListString,@startpos,@ctr-@startpos) insert into @tblINList values (@subs, @sort) set @startpos = @ctr+1 end if @ctr = @length begin select @subs = substring(@InListString,@startpos,@ctr-@startpos) insert into @tblINList values (@subs, @sort) end set @ctr = @ctr +1 set @sort = @sort + 1 end RETURN END
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
, 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 end
to pass through for your order by clausule