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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-02 : 09:11:17
|
Andrew writes "Using SQL Server 2000 (SP1) on Window 2000 (SP1)
Two tables
"daily" data -- to be searched
"mapping" data -- maps search words to "daily" columns, there is a "column" heading which says which daily column is referenced; an "operator" (e.g. =, <, > etc) and then a "value" (1, 2, 0 etc)
Notes : the mapping table is user(customer) managed with mappings being added or changed over time.
problem :
take an input search string ("itemA or itemB and itemC");
convert that to a where clause using the mapping table
e.g. (itemA maps to daily.columnA > 1; itemB maps to daily.columnD <= 10; itemC maps to daily.columnZ = 7)
apply this geneated where clause to the "daily" table and return a recordset.
Now I have written a stored procedure to do this ... but it is pretty damn horrible, and I am sure there must be a better way to do this. (I have c.1yr with SPs but no formal training).
Looking at the site it seems that cursors are a bit of a no no, but I'm &%(&^'ered if I can work out how to do this any other way.
Stored Procedure below.
CREATE Procedure searchDaily
@searchData varchar (1000)
As
--variables
declare
@sqlString nvarChar (1000),
@keyword nvarChar(60),
@columnname nvarchar(50),
@operator nvarchar(1),
@value nvarchar(10),
@replaceString nvarchar(50),
@temp nvarchar(10)
begin
/*
the search string coming in will only have listed search criteria (such as macdxtring)
this needs to be converted in to a format that can actually be executed!
*/
-- define cursor full of the mapping data
declare mappingCursor cursor for
select profileelement as keyword, columnname, operator, value from mapping
for read only
-- open this cursor
open mappingCursor
-- loop throught the cursor, replacing data as required
while ( 0 = 0 ) begin
--get the next record
fetch next
from mappingCursor
into @keyword, @columnname, @operator, @value
--have we reached an error or the end of the cursor
if ( @@Fetch_Status <> 0 ) break
--create the replace string
set @replaceString = ''
set @replaceString = @replaceString + N' [' + cast(@columnname as nvarchar(10)) + ']'
set @replaceString = @replaceString + N' ' + cast(@operator as nvarChar(10))
set @replaceString = @replaceString + N' ' + cast(@value as nvarChar(10))
--perform the replace!
set @temp = rtrim(cast(@keyword as nvarChar(10)))
set @searchData = replace(@searchData, @temp, @replaceString)
end
close mappingCursor
deallocate mappingCursor
set @sqlString = N'select distinct * from daily where ' + @searchData
--add in the discriminators for the static data -- fts100, country etc
print @sqlString
--execute the query
EXEC sp_executesql @sqlString
return ( 1000 )
end" |
|
|
|
|
|
|
|
dynamic generation of where clause (number of items not know + link to mapping table)