Stored procedure problem

11 Aug 2006 6:28 AM

Sid

How can I write a procedure where I want the following scenario
I have a field called "suburb'

create someProcedure
(
   suburb1 varchar(50),
   suburb2 varchar(50),
   suburb3 varchar(50)
)
AS
BEGIN
....
...
END
GO

I want to check in a table if records exists such that suburb matches
either suburb1,2 and 3;
1 and 2, 2 and 3, 1 and 3 1, 2 and 3. The "suburb" need not be an exact
match For ex: LIKE+'%'
Thanks

11 Aug 2006 8:15 AM

ML

Could you at least post an example of what you need or want to do?

ML

---
http://milambda.blogspot.com/

11 Aug 2006 10:41 AM

Roy Harvey

Perhaps something along these lines.

CREATE SomeProcedure
(@suburb1 varchar(50),
@suburb2 varchar(50),
@suburb3 varchar(50))
AS
BEGIN
SELECT CASE WHEN EXISTS
                 (select * from Whatever
                   where suburb LIKE @suburb1 + '%'
                      or suburb LIKE @suburb2 + '%'
                      or suburb LIKE @suburb3 + '%')
            THEN 'Yes'
            ELSE 'No'
       END as Answer
END
GO

Roy Harvey
Beacon Falls, CT

Show quote

On 10 Aug 2006 23:28:15 -0700, "Sid" <siddharth.k***@gmail.com> wrote:

>How can I write a procedure where I want the following scenario
>I have a field called "suburb'
>
>create someProcedure
>(
>   suburb1 varchar(50),
>   suburb2 varchar(50),
>   suburb3 varchar(50)
>)
>AS
>BEGIN
>...
>..
>END
>GO
>
>I want to check in a table if records exists such that suburb matches
>either suburb1,2 and 3;
>1 and 2, 2 and 3, 1 and 3 1, 2 and 3. The "suburb" need not be an exact
>match For ex: LIKE+'%'
>Thanks

11 Aug 2006 11:37 AM

Johan Sjöström

Using LIKE doesn't perform very well in the long run. If you frequently
query varchar columns, you should check out the FTS (Full Text Search)
feature. Do a search on FREETEXT or CONTAINS. Note that you have to
create full-text indexes for the tables.

E.g.

SELECT * FROM City WHERE FREETEXT(Name, 'Sto')

would find cities named Boston, Stoogeville, Restorationville,
Estoniaville etc.

Cheers,
Johan Sjöström
MSc, MCP, MCAD

Show quote

> On 10 Aug 2006 23:28:15 -0700, "Sid" <siddharth.k***@gmail.com> wrote:
>
> >How can I write a procedure where I want the following scenario
> >I have a field called "suburb'
> >
> >create someProcedure
> >(
> >   suburb1 varchar(50),
> >   suburb2 varchar(50),
> >   suburb3 varchar(50)
> >)
> >AS
> >BEGIN
> >...
> >..
> >END
> >GO
> >
> >I want to check in a table if records exists such that suburb matches
> >either suburb1,2 and 3;
> >1 and 2, 2 and 3, 1 and 3 1, 2 and 3. The "suburb" need not be an exact
> >match For ex: LIKE+'%'
> >Thanks