[dba-SQLServer] SP works sometimes

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