dynamic sql

[terpster73]

I have the following and I need to have the variables 'search' for data that 'contains' the data a user enters. Eg. For either OWNNAME1 or OWNNAME2 A user types in mont and they would get records that matched Montana, and Fairmont. How can I Acheive this with what I already have?

DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OWNNAME1, OWNNAME2, OWNADD1, OWNADD2, OWNCITY, OWNSTATE, OWNERZIP ' +
' FROM dbo.HOWA WHERE 1 = 1 '
IF @OWNNAME1 IS NOT NULL
SELECT @sql = @sql + ' AND OWNNAME1 LIKE @OWNNAME1 '
IF @OWNNAME2 IS NOT NULL
SELECT @sql = @sql + ' AND OWNNAME2 LIKE @OWNNAME2 '
EXEC sp_executesql @sql, N'@OWNNAME1 nvarchar(100), @OWNNAME2 nvarchar(100)',
@OWNNAME1, @OWNNAME2

 

[Crowley16]

in situations like these, I usually do something like:

select
*
from
table
where
col1 like isnull('%' + @param1 + '%', col1)
and col2 like isnull('%' + @param2 + '%', col2)

--------------------
Procrastinate Now!

 

[bborissov]

or:

select *
from table
where col1 like '%' + isnull(@param1,'') AND
      col2 like '%' + isnull(@param2,'')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Crowley16]

that won't catch any trailing characters boris...

--------------------
Procrastinate Now!

 

[bborissov]

Are you sure:
(That example was prepared for other post)

DECLARE @Test TABLE (ElapsedTime nvarchar(200))
INSERT INTO @Test VALUES('PS:91 PO:0 FC:0 TC:296 TT:387')
INSERT INTO @Test VALUES('PS:66 PO:0 FC:0 TC:246 TT:312')
INSERT INTO @Test VALUES('PS:76 PO:0 FC:0 TC:21 TT:97')
INSERT INTO @Test VALUES('PS:159 PO:0 FC:0 TC:192 TT:351')
INSERT INTO @Test VALUES('PS:52 PO:0 FC:0 TC:224 TT:276')
INSERT INTO @Test VALUES('PS:88 PO:0 FC:0 TC:252 TT:340')
INSERT INTO @Test VALUES('PS:44 PO:0 FC:0 TC:214 TT:258')
INSERT INTO @Test VALUES('PS:75 PO:0 FC:0 TC:20 TT:95')
INSERT INTO @Test VALUES('PS:49 PO:0 FC:0 TC:225 TT:274')
INSERT INTO @Test VALUES('PS:100 PO:0 FC:0 TC:219 TT:319')
INSERT INTO @Test VALUES('PS:1000000 PO:-123120 FC:+12431230 TC:333219 TT:123319')


DECLARE @tst varchar(200) -- @tst is NULL

select ElapsedTime as d
from @Test
WHERE ElapsedTime LIKE '%'+ISNULL(@tst,'')

When @tst is NULL you end up with this where clause:

WHERE ElapsedTime LIKE '%'

which means "I want it ALL" (like Freddy Mercury sings)



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

But me and you was wrong other way.
After I re-read terpster73 post, I think He/She
wants ALL records which are equal to (or begin with) the parameter value if the parameter is passed, so:

select *
from table
where col1 like isnull(@param1,'') + '%'
  and col2 like isnull(@param2,'') + '%'

or if terpster73 wants the equals:

select *
from table
where col1 = isnull(@param1,col1)
  and col2 = isnull(@param2,col2)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[terpster73]

Let me explain a little more what I'm looking for. The first example worked wonderfully when I was using only two parameters. I should have mentioned in my original posting that I am going to need to be able to search on 8 different fields.

name1
name2
address1
address2
city
state
zip
county


At any point they can give me information for any or all of the fields.

In addition, I've now been asked to use contains instead of like in the searches.

 

[SQLSister]

Contains means you must have full text search set up. Do you? You will also need to have a plan in place to keep the fulltext index up to date. Before going any further, go to Books online and read about fulltext search.

"NOTHING is more important in a database than integrity." ESquared