Problem with Full Text Search and Joined Tables

I have a sproc that accepts variables from an ASP page to process the
search for the user. The user can choose 3 methods:
1.)Any Occurence
2.)Word/Phrase
3.)Exact Word/Phrase
The sproc goes through some if statments to decide which sql statement
to execute based on the method chosen by the user. The user can also
type in a keyword (of course) but can apply the key word to one column
or two columns. The columns are Name and Description (even though
they may not be Name or Description in the database...just a generic
name to use). Since the sproc is not my problem i'll just give a
little exampe of how it works instead of posting all that code, heh.
if method = 1
Begin
if name and description = 1
Begin
execute sql
End
if name = 1
Begin
execute sql
End
if description = 1
Begin
execute sql
End
End
And the code would be the same for the next two methods. My problem
is with the sql statment that executes when the name and description
are equal to 1. I create views and usually do a join between the view
and the table i want to search on. Then i return the results and that
works fine. The problem with this search is that the Name column is
in one table and the Description in another. I still need the view
cause it contains the other 15 tables of info i use to display and
filter data. But i don't know how to search on both tables and get
the resutls i want. Here is my code so far...
Set @.stmt = 'Select * From cuPolicyMemosView
RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
@.strSearchLike + '"' + "'" + ') AS Search ON
cuPolicyMemosView.PolicyMemosID = Search.[KEY]
LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
'"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
ORDER BY ' + @.strSort
Exec(@.stmt)
I can get the Name just fine when i do a search. But i can't get the
Description. I've tried RIGHT, LEFT, FULL joins...can't get the
Description. I've heard that the only way might be to put the info i
get back from each table seperatley and the put that info in a temp
table and search off of that. That might be a possiblity, but i'm not
100% sure how to do it, heh. I would rather just have a way to alter
my current SQL statment, but if that can't be done then i'm open to
any other suggestions. I hope this all makes sense, heh, i know its a
little unclear...but if anyone has dealt with the joined tables search
problem and knows a good way around that problem in general then thats
all the help i need. Thanks in advance.
Taylor
Taylor,
I've read your posting with interest and it seems that this is the main
issue: "The problem with this search is that the Name column is
in one table and the Description in another. I still need the view cause it
contains the other 15 tables of info i use to display and filter data. But
i don't know how to search on both tables and get the results i want."
The problem of searching across tables with SQL FTS is well know as I posted
three times on this issue just yesterday... I've attached a SQL script file
(Containstable_multiple_tables.sql) with schema and inserted data that I
*think* is related to what you are trying to do, although it does not use
dynamic SQL code... Also, in your code snippet example below could you
provide the name of the view &/or code. If that's not possible, you can just
create a similar view with new tables that work with the tables in the
attached file.
Regards,
John
PS: If you cannot access the attached file, feel free to email me directly
and I'll email it to you.
"Taylor" <dryrye@.juno.com> wrote in message
news:9c0e390c.0409010657.4167393d@.posting.google.c om...
> I have a sproc that accepts variables from an ASP page to process the
> search for the user. The user can choose 3 methods:
> 1.)Any Occurence
> 2.)Word/Phrase
> 3.)Exact Word/Phrase
> The sproc goes through some if statments to decide which sql statement
> to execute based on the method chosen by the user. The user can also
> type in a keyword (of course) but can apply the key word to one column
> or two columns. The columns are Name and Description (even though
> they may not be Name or Description in the database...just a generic
> name to use). Since the sproc is not my problem i'll just give a
> little exampe of how it works instead of posting all that code, heh.
> if method = 1
> Begin
> if name and description = 1
> Begin
> execute sql
> End
> if name = 1
> Begin
> execute sql
> End
> if description = 1
> Begin
> execute sql
> End
> End
> And the code would be the same for the next two methods. My problem
> is with the sql statment that executes when the name and description
> are equal to 1. I create views and usually do a join between the view
> and the table i want to search on. Then i return the results and that
> works fine. The problem with this search is that the Name column is
> in one table and the Description in another. I still need the view
> cause it contains the other 15 tables of info i use to display and
> filter data. But i don't know how to search on both tables and get
> the resutls i want. Here is my code so far...
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
> @.strSearchLike + '"' + "'" + ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
> '"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
> ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
> ORDER BY ' + @.strSort
> Exec(@.stmt)
> I can get the Name just fine when i do a search. But i can't get the
> Description. I've tried RIGHT, LEFT, FULL joins...can't get the
> Description. I've heard that the only way might be to put the info i
> get back from each table seperatley and the put that info in a temp
> table and search off of that. That might be a possiblity, but i'm not
> 100% sure how to do it, heh. I would rather just have a way to alter
> my current SQL statment, but if that can't be done then i'm open to
> any other suggestions. I hope this all makes sense, heh, i know its a
> little unclear...but if anyone has dealt with the joined tables search
> problem and knows a good way around that problem in general then thats
> all the help i need. Thanks in advance.
> Taylor
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end
|||Here's my stab at this. Note that you have to change the @.separator value
from ' AND' to ' OR ' when you want to change from all the words to some of
the words.
create database test
go
use test
go
create table PolicyMemos
(
PolicyMemosID int not null primary key identity(1,1),
[Name] varchar(200),
ExpirationDate datetime
)
go
create table PolicyMemosFormNumber
(
PMFNID int not null primary key identity(1,1),
PolicyMemosID int not null foreign key references
policyMemos(PolicyMemosID ),
[Description] varchar(200)
)
go
alter View cuPolicyMemosView
as
select [Name], ExpirationDate, [Description]
,[policymemosid]=PolicyMemos.PolicyMemosID,PMFNID
from PolicyMemos, PolicyMemosFormNumber
where PolicyMemos.PolicyMemosID =PolicyMemosFormNumber.PolicyMemosID
go
sp_fulltext_database 'enable'
go
sp_fulltext_catalog 'test','create'
go
declare @.indexname varchar(130)
select @.indexname = name from sysindexes where id=object_id('PolicyMemos')
exec sp_fulltext_table 'PolicyMemos', 'create', 'test', @.indexname
go
sp_fulltext_column 'PolicyMemos','Name','add',1033
go
sp_fulltext_table 'PolicyMemos', 'activate'
go
sp_fulltext_table 'PolicyMemos', 'start_full'
go
declare @.indexname varchar(130)
select @.indexname = name from sysindexes where
id=object_id('PolicyMemosFormNumber')
exec sp_fulltext_table 'PolicyMemosFormNumber', 'create', 'test', @.indexname
go
sp_fulltext_column 'PolicyMemosFormNumber','Description','add',1033
go
sp_fulltext_table 'PolicyMemosFormNumber', 'activate'
go
sp_fulltext_table 'PolicyMemosFormNumber', 'start_full'
go
insert into PolicyMemos ([name], expirationdate) values('New York City',
getdate()-20)
go
insert into PolicyMemos ([name], expirationdate) values('Manhattan',
getdate()-30)
go
insert into PolicyMemos ([name], expirationdate) values('Jersey City',
getdate()-30)
go
insert into PolicyMemos ([name], expirationdate) values('Englishtown',
getdate()-30)
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of water table levels')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of soil samples')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(1,'study of granite formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of shale formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of marble formations')
go
insert into PolicyMemosFormNumber (PolicyMemosID , description )
values(2,'study of top soil')
go
declare @.strSort varchar(20)
set @.strSort ='Name'
declare @.stmt varchar(2000)
declare @.strSearchLike varchar(200)
declare @.strSearchParsed varchar(200)
declare @.separator varchar(5)
set @.separator=' OR '
set @.strSearchLike ='manhattan soil'
select @.strSearchLike=case when left(@.strSearchLike,1)=char(32) then
substring(@.strSearchLike,2,len(@.strSearchLike)-1) else @.strSearchLike end
set @.strSearchParsed=''
select @.strSearchlike =replace(@.strsearchLike,' ',char(32))
declare @.counter int
declare @.NumberOfWhiteSpaces int
set @.NumberOfWhiteSpaces=len(@.strSearchLike)-len(replace(@.strSearchLike,'
',''))
set @.counter=@.NumberOfWhiteSpaces
while @.counter >0
begin
select @.strSearchParsed=@.strSearchParsed+left(@.strSearchL ike, charindex('
',@.strSearchLike)-1)+ char(34)+@.separator+char(34)
if @.counter<>0
select @.strSearchlike=substring(@.strSearchLike,charindex( '
',@.strSearchLike)+1,len(@.strSearchLike))
select @.counter=@.counter-1
end
select @.strSearchParsed=char(34)+ @.strSearchParsed+
char(32)+@.strSearchLike+char(34)
Set @.stmt = 'Select * From cuPolicyMemosView
RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' ++char(39)+
@.strSearchParsed + char(39)+ ') AS Search ON
cuPolicyMemosView.PolicyMemosID = Search.[KEY]
LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + char(39) +
@.strSearchParsed +char(39) + ') AS Search2 ON
cuPolicyMemosView.PMFNID = Search2.[KEY]
--Where ((DATEDIFF(d,ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate
IS NULL))
ORDER BY ' + @.strSort
exec (@.stmt)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Taylor" <dryrye@.juno.com> wrote in message
news:9c0e390c.0409010657.4167393d@.posting.google.c om...
> I have a sproc that accepts variables from an ASP page to process the
> search for the user. The user can choose 3 methods:
> 1.)Any Occurence
> 2.)Word/Phrase
> 3.)Exact Word/Phrase
> The sproc goes through some if statments to decide which sql statement
> to execute based on the method chosen by the user. The user can also
> type in a keyword (of course) but can apply the key word to one column
> or two columns. The columns are Name and Description (even though
> they may not be Name or Description in the database...just a generic
> name to use). Since the sproc is not my problem i'll just give a
> little exampe of how it works instead of posting all that code, heh.
> if method = 1
> Begin
> if name and description = 1
> Begin
> execute sql
> End
> if name = 1
> Begin
> execute sql
> End
> if description = 1
> Begin
> execute sql
> End
> End
> And the code would be the same for the next two methods. My problem
> is with the sql statment that executes when the name and description
> are equal to 1. I create views and usually do a join between the view
> and the table i want to search on. Then i return the results and that
> works fine. The problem with this search is that the Name column is
> in one table and the Description in another. I still need the view
> cause it contains the other 15 tables of info i use to display and
> filter data. But i don't know how to search on both tables and get
> the resutls i want. Here is my code so far...
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' + "'" + '"' +
> @.strSearchLike + '"' + "'" + ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + "'" +
> '"' + @.strSearchLike + '"' + "'" + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY] Where ((DATEDIFF(d,
> ExpirationDate + 30, GETDATE()) < 0) OR (ExpirationDate IS NULL))
> ORDER BY ' + @.strSort
> Exec(@.stmt)
> I can get the Name just fine when i do a search. But i can't get the
> Description. I've tried RIGHT, LEFT, FULL joins...can't get the
> Description. I've heard that the only way might be to put the info i
> get back from each table seperatley and the put that info in a temp
> table and search off of that. That might be a possiblity, but i'm not
> 100% sure how to do it, heh. I would rather just have a way to alter
> my current SQL statment, but if that can't be done then i'm open to
> any other suggestions. I hope this all makes sense, heh, i know its a
> little unclear...but if anyone has dealt with the joined tables search
> problem and knows a good way around that problem in general then thats
> all the help i need. Thanks in advance.
> Taylor
|||note that in this statement select @.strSearchlike =replace(@.strsearchLike,'
',char(32))
I am replacing two consecutive white spaces with a single white space.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ezWC7UPkEHA.2412@.TK2MSFTNGP15.phx.gbl...
> Here's my stab at this. Note that you have to change the @.separator value
> from ' AND' to ' OR ' when you want to change from all the words to some
of
> the words.
> create database test
> go
> use test
> go
> create table PolicyMemos
> (
> PolicyMemosID int not null primary key identity(1,1),
> [Name] varchar(200),
> ExpirationDate datetime
> )
> go
> create table PolicyMemosFormNumber
> (
> PMFNID int not null primary key identity(1,1),
> PolicyMemosID int not null foreign key references
> policyMemos(PolicyMemosID ),
> [Description] varchar(200)
> )
> go
> alter View cuPolicyMemosView
> as
> select [Name], ExpirationDate, [Description]
> ,[policymemosid]=PolicyMemos.PolicyMemosID,PMFNID
> from PolicyMemos, PolicyMemosFormNumber
> where PolicyMemos.PolicyMemosID =PolicyMemosFormNumber.PolicyMemosID
> go
> sp_fulltext_database 'enable'
> go
> sp_fulltext_catalog 'test','create'
> go
> declare @.indexname varchar(130)
> select @.indexname = name from sysindexes where id=object_id('PolicyMemos')
> exec sp_fulltext_table 'PolicyMemos', 'create', 'test', @.indexname
> go
> sp_fulltext_column 'PolicyMemos','Name','add',1033
> go
> sp_fulltext_table 'PolicyMemos', 'activate'
> go
> sp_fulltext_table 'PolicyMemos', 'start_full'
> go
> declare @.indexname varchar(130)
> select @.indexname = name from sysindexes where
> id=object_id('PolicyMemosFormNumber')
> exec sp_fulltext_table 'PolicyMemosFormNumber', 'create', 'test',
@.indexname
> go
> sp_fulltext_column 'PolicyMemosFormNumber','Description','add',1033
> go
> sp_fulltext_table 'PolicyMemosFormNumber', 'activate'
> go
> sp_fulltext_table 'PolicyMemosFormNumber', 'start_full'
> go
> insert into PolicyMemos ([name], expirationdate) values('New York City',
> getdate()-20)
> go
> insert into PolicyMemos ([name], expirationdate) values('Manhattan',
> getdate()-30)
> go
> insert into PolicyMemos ([name], expirationdate) values('Jersey City',
> getdate()-30)
> go
> insert into PolicyMemos ([name], expirationdate) values('Englishtown',
> getdate()-30)
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of water table levels')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of soil samples')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(1,'study of granite formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of shale formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of marble formations')
> go
> insert into PolicyMemosFormNumber (PolicyMemosID , description )
> values(2,'study of top soil')
> go
>
> declare @.strSort varchar(20)
> set @.strSort ='Name'
> declare @.stmt varchar(2000)
> declare @.strSearchLike varchar(200)
> declare @.strSearchParsed varchar(200)
> declare @.separator varchar(5)
> set @.separator=' OR '
> set @.strSearchLike ='manhattan soil'
> select @.strSearchLike=case when left(@.strSearchLike,1)=char(32) then
> substring(@.strSearchLike,2,len(@.strSearchLike)-1) else @.strSearchLike end
> set @.strSearchParsed=''
> select @.strSearchlike =replace(@.strsearchLike,' ',char(32))
> declare @.counter int
> declare @.NumberOfWhiteSpaces int
> set @.NumberOfWhiteSpaces=len(@.strSearchLike)-len(replace(@.strSearchLike,'
> ',''))
> set @.counter=@.NumberOfWhiteSpaces
> while @.counter >0
> begin
> select @.strSearchParsed=@.strSearchParsed+left(@.strSearchL ike, charindex('
> ',@.strSearchLike)-1)+ char(34)+@.separator+char(34)
> if @.counter<>0
> select @.strSearchlike=substring(@.strSearchLike,charindex( '
> ',@.strSearchLike)+1,len(@.strSearchLike))
> select @.counter=@.counter-1
> end
> select @.strSearchParsed=char(34)+ @.strSearchParsed+
> char(32)+@.strSearchLike+char(34)
> Set @.stmt = 'Select * From cuPolicyMemosView
> RIGHT OUTER JOIN CONTAINSTABLE(PolicyMemos, Name, ' ++char(39)+
> @.strSearchParsed + char(39)+ ') AS Search ON
> cuPolicyMemosView.PolicyMemosID = Search.[KEY]
> LEFT JOIN CONTAINSTABLE(PolicyMemosFormNumber, Description, ' + char(39)
+
> @.strSearchParsed +char(39) + ') AS Search2 ON
> cuPolicyMemosView.PMFNID = Search2.[KEY]
> --Where ((DATEDIFF(d,ExpirationDate + 30, GETDATE()) < 0) OR
(ExpirationDate
> IS NULL))
> ORDER BY ' + @.strSort
> exec (@.stmt)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Taylor" <dryrye@.juno.com> wrote in message
> news:9c0e390c.0409010657.4167393d@.posting.google.c om...
>