Stored Proc - Select Where @Field like @KeyWord

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Proc - Select Where @Field like @KeyWord

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-05-04 : 14:42:26

Having trouble with this sp and hope someone can help.
This below WILL work ok...

CREATE PROCEDURE dbo.search

@Field VARCHAR (20),
@KeyWord VARCHAR (200),
@errCode INT OUTPUT

AS
BEGIN

DECLARE @guid uniqueidentifier
DECLARE @subject NVARCHAR

DECLARE @tkeyword VARCHAR (200)

SET @tkeyword = '%' + @KeyWord + '%'

SELECT @guid = guid, @subject = subject
FROM myTable WHERE subject LIKE @tkeyword

SELECT guid, subject
FROM myTable WHERE subject LIKE @tkeyword

Problem is, the param @Field is also being passed as the field to search. If I hard code the field, i.e. subject, it works like above.

But try to add the other param and I am getting no where.

Both select statements are needed to get the dataset (C#.Net).

So, how can I get something like this to work?????

SELECT @guid = guid, @subject = subject
FROM myTable WHERE @Field LIKE @tkeyword

SELECT guid, subject
FROM myTable WHERE @Field LIKE @tkeyword

Thanks,

Zath

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-04 : 14:45:39

declare @command = "select blah-blah-blah from table where ' + @field + 'like ' + @keyword
exec @command

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-05-04 : 14:54:55

I already tried that and it just wouldn't work...

declare @mysqlstring varchar(500)

select @mysqlstring='SELECT @guid = guid, @subject = subject
FROM myTable WHERE ' + @Field + ' like ' + @tkeyword

exec(@mysqlstring)

declare @mysqlstring2 varchar(500)

select @mysqlstring2='SELECT guid, subject
FROM myTable WHERE ' + @Field + ' like ' + @tkeyword

This won't work.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-04 : 14:59:19

Duh!!! (to me)

You would need to wrap the @keyword with quotes and a %.

'like ''' + @keyword + '%''''

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-05-04 : 15:00:58

It is.... see original post...

SET @tkeyword = '%' + @KeyWord + '%'

Zath

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-04 : 15:16:07

That just wrapped it with the %, you'd still need the forced single quotes around the keyword. Meaning now it would be:

field like %keyword%

and you want the output to be:

field like '%keyword%'

So you'll need:

'like ''' + @keyword + ''''

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-05-04 : 15:28:13

Sorry, that didn't help either.

I've tried everything and for the first select, I get must declare @guid. Ummmm, thought I did.

For the second select, it thinks my keyword is a column name....

Only 4 fields and I hate to make 4 if statements just to get this thing working.

BTW, are those single, double, or what quotes you have?
Can you rewrite and spread them out a bit for better view?

Thanks,

Zath

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-04 : 15:34:38

They are single quotes:

'like ' ' ' + @keyword + ' ' ' '

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-05-04 : 16:22:05

Well, I do thank you for the help but just can't seem to get it to work the way I wanted.

Hated to do it this way.... just wrote 4 if statements. One for each possible field that the @Field could be.

Zath

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-04 : 16:28:44

quote:

Hated to do it this way.... just wrote 4 if statements. One for each possible field that the @Field could be.

That actually will perform better than doing it dynamically. Using dynamic SQL should be avoided usually as it will suffer from poorer performance and weaker security. Since you can write it with 4 IF statements, that is the way to go. There are times where you can't avoid dynamic SQL, just not in this case.

Tara Kizer
aka tduggan

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-05-05 : 08:00:20

Interesting. I guess you learn something new everyday.
I don't claim to be a db expert, I'm a programmer but can get around databases pretty well.

But thanks for the info.

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources