Mark A Matte
markamatte at hotmail.com
Tue Oct 24 16:14:43 CDT 2006
Arthur,
Thanks again, but I just sent the first example I had...I had not heard of
'regex' and your statement "<last one doesn't fit the pattern"> really
confused me...but after researching it I realised the logic behing the
suggestion. In the example I gave I was trying to find reference to a
company named 'HAIER' pronounced (hire or hare)...and those were the
variations I came up with...the last was a model number...so if the record
made any reference to the company or model I wanted it. Below is a better
example or what I use.
Thanks again,
Mark A. Matte
@ID=((rc_descrlong Like '% ks%' or rc_descrlong Like '%kans%') And
rc_descrlong Like '%water%' And rc_descrlong Like '%heater%')
>From: <artful at rogers.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] SP works sometimes
>Date: Tue, 24 Oct 2006 13:50:53 -0700 (PDT)
>
>Thanks for the illumination. Now I understand the problem a little better.
>As I read your example I was becoming convinced that regex might be a
>better approach, but the last one doesn't fit the pattern. However, if your
>example really does illustrate the typical similarity of your search
>targets then regex is worth a look.
>
>Arthur
>
>----- Original Message ----
>From: Mark A Matte <markamatte at hotmail.com>
>To: dba-sqlserver at databaseadvisors.com
>Sent: Monday, October 23, 2006 10:41:57 PM
>Subject: Re: [dba-SQLServer] SP works sometimes
>
>Thank both of you very much for your feedback. I'll start with what I'm
>trying to do. I have the need to query up 6 fields for different
>variations
>of different words using wild cards...sometimes upto 40 different sql
>statements...so my use of >"'+ at ID+'" as Criteria is necesarry to have singe
>and double quotes...because @ID is actually the criteria that was used to
>isolate the record...since the text is actually the criteria part of my
>SQL...I have to surround it with double quotes.
>
>Now for the creation_date>"07/01/2006" and '+ at id part of things...as I said
>my @ID is actually the end of my criteria. below is an example of what is
>passed:
>
>@ID=(rc_descrlong Like '%haier%' or rc_descrlong Like '%higher%' or
>rc_descrlong Like '%haer%' or rc_descrlong Like '%hire%' or rc_descrlong
>Like '%hair%' or rc_descrlong Like '%hare%' or rc_descrlong Like '%harr%'
>or
>rc_descrlong Like '%42ep24%')
>
>So...I have upto 40 statements like this one with different words and
>different fields. I use this SP so I can run all 40 SQL statements in a
>row
>without having to sit and push the buttton 40 times.
>
>If there is a better way please share...SQL I know...SQL Server I am very
>new to.
>
>Thanks,
>
>Mark A. Matte
>
>
> >From: <artful at rogers.com>
> >Reply-To: dba-sqlserver at databaseadvisors.com
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: Re: [dba-SQLServer] SP works sometimes
> >Date: Mon, 23 Oct 2006 16:51:00 -0700 (PDT)
> >
> >Quite right, Doris. But I think there is something else missing. The last
> >part makes no sense, I think:
> >
> >where creation_date>"07/01/2006" and '+ at id
> >
> >This makes no sense. It could have been a simple typo slip-up, but if not
> >then how is this going to parse into anything sensible? The AND is going
>to
> >make any such statement incomprehensible.
> >
> >A larger question: Why bother dynamically creating a SQL statement to do
> >this thing, when you could quite easily create a variant of this sproc
>that
> >drops the params into the statement, rather than constructing the
>statement
> >and executing it? Perhaps I'm having a senior moment, but I can't fathom
> >the need to do it this way. You don't have to build a statement just
> >because you're doing a nested SELECT.
> >
> >Or perhaps I'm missing something. It wouldn't be the first time.
> >
> >Arthur
> >
> >CREATE PROCEDURE [Select_Records_1]
> >( @ID nvarchar(255)
> >)
> >AS
> >INSERT INTO tblHaier_ID
> >(case_id, Criteria)
> >SELECT Case_id
> >declare @sql as varchar(1000)
> >declare @nsql as nchar(3000)
> >set @sql='insert into tblHaier_ID(case_id,Criteria) SELECT case_id,
> >"'+ at ID+'" as Criteria FROM tblCase where creation_date>"07/01/2006" and
> >'+ at id
> >set @nsql=@sql
> >set nocount on
> >exec sp_executesql @nsql
> >set nocount off
> >
> >
> >----- Original Message ----
> >From: Doris Manning <mikedorism at verizon.net>
> >To: dba-sqlserver at databaseadvisors.com
> >Sent: Monday, October 23, 2006 6:21:39 PM
> >Subject: Re: [dba-SQLServer] SP works sometimes
> >
> >Your problems lie in your mixing of single quotes (') and double quotes
> >(").
> >You don't use double quotes (") in T-SQL statements. Dates in SQL are
> >surrounded in single quotes (').
> >
> >I've also found it helpful to preface all strings with the N designator
>to
> >be sure they are interpreted properly.
> >
> >Doris Manning
> >
> >
> >_______________________________________________
> >dba-SQLServer mailing list
> >dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
> >
> >
> >
> >
> >
> >_______________________________________________
> >dba-SQLServer mailing list
> >dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
> >
>
>_________________________________________________________________
>All-in-one security and maintenance for your PC. Get a free 90-day trial!
>http://clk.atdmt.com/MSN/go/msnnkwlo0050000002msn/direct/01/?href=http://www.windowsonecare.com/?sc_cid=msn_hotmail
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
_________________________________________________________________
Find a local pizza place, music store, museum and more…then map the best
route! http://local.live.com?FORM=MGA001