multiple paramters - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

multiple paramters

Author

Topic

mirza
Starting Member

7 Posts

Posted - 2012-07-06 : 08:52:11

I need to have keywords search. Keywords are divided by comma. There is a table called tags. My code in C# looks like following:

query = " SELECT * FROM Document WHERE AND Tags IN'(' + @Tags + ')')";
string[] words = Tags.Text.Split(',');//create an array of characters

foreach (string word in words)
{
cmd.Parameters.AddWithValue("Tags", "%" + word.Trim() + "%");
}

But there is something wrong. Can you help me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 09:21:46

make the query like

" SELECT * FROM Document WHERE AND ','"+ @Tags + "',' LIKE '%,' + Tags + ',%' ";

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 09:29:44

To add to what Visakh said - if you do it that way, you wouldn't split the data in Tags.Text into its own tokens. That would give you a run-time error if you have more than one token (because you will be calling AddWithValue with the same parameter more than once). So you code would be something like this:

query ="SELECT * FROM Document WHERE AND ','"+ @Tags + "',' LIKE '%,' + Tags + ',%' ";
cmd.Parameters.AddWithValue("Tags", Tags.Text);

That does not take care of trailing or leading spaces on the tokens though.

mirza
Starting Member

7 Posts

Posted - 2012-07-06 : 10:16:12

Can i do it like this
query = " SELECT * FROM Document WHERE Tags LIKE " word (0) LIKE "word (1) ";as far as many words exist???

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 10:27:42

Yes, you can do "something" like that. Your code should probably be this:

query = " SELECT * FROM Document WHERE (0 = 1) "
string[] words = Tags.Text.Split(',');//create an array of characters

foreach (string word in words)
{
   query += " OR ( Tag = ''" + word.Trim() + "'')" ;
}

In this case, you would not add any parameter at all. If that does not work, can you print the query string after the foreach loop and post it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources