Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need predefined array for sorting++ in sproc

Author  Topic 

tiwas
Starting Member

37 Posts

Posted - 2012-12-22 : 16:33:08
Hi guys,

First of all - thanks for all the help in the past. It has really made my life easier! :)

I have a new problem, and as far as I can see there are no easy fixes for it.

What I need is to define an array in my sproc, like {'first', 'second', 'third'}. I then need to add this to my where clause like 'where val in @array'. And then I need to order the lines according to the placement in the array.

If easier, I can also use a temp table where I use an integer for ordering, but I'm unsure about how to use that too...

Would it be just as easy for me to make a temp table, insert the values and orders and just join it to my result and then user the temp_table.order in my order by clause?

Cheers!

tiwas
Starting Member

37 Posts

Posted - 2012-12-22 : 16:56:15
Never mind - I think I managed once I got my thouhts sorted. Helpt writing things down ;)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-23 : 01:41:58
you've different options

1. use a string parsing UDF and get the array returned as a table of values and use it in join to do filtering

so in your case

SELECT...
FROM...
...your rest of the query
INNER JOIN dbo.ParseValues('first,second,third',',')f
ON f.Val = Yourtable.Field


ParseValues can be found in below link

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html


2. Use string based comparison like

DECLARE @FilterList varchar(8000)

SET @FiletrList = 'First,Second,Third'

SELECT...
...
rest of query
WHERE ',' + @FilterList + ',' LIKE '%,' + YourTablefield + ',%'



there are few other methods also like XML method which is discussed in below link

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -