Programming help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Programming help

Author

Topic

meditdba
Starting Member

7 Posts

Posted - 2009-06-11 : 13:33:46

I need an advice on how to do implement certain logic. Let's say I have table Customer that has columns Col1, Col2, Col3, and Col4. I need to be able to get date from this table, but the data returned must be based on ranked filter defined in table Filter.

For example the data in Filter is as follows

Row1
Rank = 1
Col1Value = A
Col2Value = B
Col3Value = null
Col4Value = null

Row2
Rank = 2
Col1Value = A
Col2Value = B
Col3Value = C
Col4Value = null

Row 3
Rank = 3
Col1Value = null
Col2Value = null
Col3Value = C
Col4Value = D

Row N - as so on

Currently, I am doing a cursor on Filter ordered by Rank, and then for each record in the cursor I create dynamic sql with WHERE statement that matches columns in table Customer to Filter values. If select statement has rows then I return rows to the user and I am done. If it doesn't have rows then I go the next record in the filter.

At this point my Filter can contain ranks going up to 30, so if rows are matched for filter with rank 29, this process takes a long time to finish, since it first executes select for the first 28 filter options.

Any help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 13:42:17

why use cursor? you just need only this

SELECT * FROM YourTable WHERE Rank=29

meditdba
Starting Member

7 Posts

Posted - 2009-06-11 : 14:04:18

quote:
Originally posted by visakh16

why use cursor? you just need only this

SELECT * FROM YourTable WHERE Rank=29

My Customer table does not have Rank column, it just has the fields that I need to filter based on. I need to find an rows that match filter as defined by Rank 1, if not found then Rank 2, etc... First filter that matches some records in Customer table gets returned to user, and we are done.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-11 : 14:44:55

How is Rank defined? Here is a link on how to post your database question with DDL, Sample Data and expected output. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 02:54:37

quote:
Originally posted by meditdba

quote:
Originally posted by visakh16

why use cursor? you just need only this

SELECT * FROM YourTable WHERE Rank=29

My Customer table does not have Rank column, it just has the fields that I need to filter based on. I need to find an rows that match filter as defined by Rank 1, if not found then Rank 2, etc... First filter that matches some records in Customer table gets returned to user, and we are done.

so on what basis you generate the rank? you could use window functions like rank(),dense_rank() for generating this on the fly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources