T-SQL question

markeh · 23 Mar 2009 at 16:17

Hi,

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

Code:

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? :confused:

Thanks,

Pho · 23 Mar 2009 at 16:20

You could use a fast-forward cursor (/awaits cursors are evil comments) - they'll allow you to checek each row and increment if needed.

http://msdn.microsoft.com/en-us/library/aa172573(SQL.80).aspx

Ciphon · 23 Mar 2009 at 17:11

Code:

WHERE	table.SD_FirstName LIKE '%Joe%' OR table.SD_Surname LIKE '%Bloggs%'

Need the %'s also, as above, don't you?

nikebee · 23 Mar 2009 at 17:40

Just a question but why do you want to use a variable there?

What do you want to do with it?

From a quick scan of what you've got, you could do that with identity insert and a temporary table.

Code:

declare @table table (id int, sd_surname nvarchar(255), SD_FirstName nvarchar(255), SD_Initials nvarchar(255), SD_Born nvarchar(255), SD_Resident nvarchar(255), rank identity int)
insert into @table (id, sd_surname, SD_FirstName, SD_Initials, SD_Born, SD_Resident)
SELECT	table.ID, table.SD_Surname, table.SD_FirstName,table.SD_Initials,
		table.SD_Born, table.SD_Resident
FROM	table
WHERE	table.SD_FirstName LIKE '%Joe%' OR table.SD_Surname LIKE '%Bloggs%'

Rank would start at 1 and increment each time a record was added.

Ciphon is also correct, you'll need %%'s in there otherwise your like statement is basically doing an equals statement.

Like 'JOE'
is the same as
= 'JOE'

Using %'s gives it a wildcard. A % before your word, means anything before 'Joe' ('%Joe') and after the word means anything after ('JOE%')

oneilldo · 23 Mar 2009 at 19:15

and double wildcard is the route of all evil unless your table is small and you dont mind a table scan.

markeh · 24 Mar 2009 at 09:36

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.