SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need predefined array for sorting++ in sproc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tiwas
Starting Member

Norway
37 Posts

Posted - 12/22/2012 :  16:33:08  Show Profile  Reply with Quote
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

Norway
37 Posts

Posted - 12/22/2012 :  16:56:15  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/23/2012 :  01:41:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.5 seconds. Powered By: Snitz Forums 2000