T-SQL question

[markeh]

Hi,

I'm trying to select a set of records from sql server, something like:

DECLARE @iRank INT
SET @iRank = 0


SET NOCOUNT ON

SELECT	table.ID, table.SD_Surname, table.SD_FirstName,table.SD_Initials,
		table.SD_Born, table.SD_Resident, @iRank AS iRank
FROM	table
WHERE	table.SD_FirstName LIKE 'Joe' OR table.SD_Surname LIKE 'Bloggs'

What I want to do is whenever table.SD_FirstName = 'Joe' or table.SD_Surname = 'Bloggs' that iRank is incremented by 1 (but keeping the likes in the where clause)

Does that make sense?

Thanks,

 

[markeh]

Thanks guys, I was in a bit of rush when I typed that (hence the lack of wildcards for the LIKE but thanks for the reminder ).

It's a stored proc being called from some asp by an easy SQL="exec sp_TheProc" which is then put in a recordset and looped through.

What I want to do is find all matches 'like' the search parameters, but those that match exactly to having a ranking point added so those that match all 5 parameters would get 5, those that mtach 4 get 4 etc. This would be used for sorting so the most relevant matches are displayed on the top.