[SQL] One parameter and few values.

Hello!

I have tables "forum_topics" and "forum_categories":

forum_topics:
-topic_id
-topic_title
-topic_cat_id
-topic_user_id
-topic_date

forum_categories:
-cat_id
-cat_name

For example: I have 5 categories in table forum_categories and by this query I can display topics from 2nd and 3rd category only. So I'm using this query:
SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (2;3)

Now I want to parametrized this query by one parameter, so:

SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (@.param)

And I want to input this parameter lik in the query first - so @.param = "2;3"

But I have a problem to make this works, can someone explain me, how to solve this problem ? Thanks.

Czesc Grzesiek,

The quick and dirty way of doing it is to convert your SQL to a string and execute it later, this is useful when you have optional parameters. Your stor proc would look something like that:

Declare @.sqlas varchar(1000)SET @.sql ='SELECT topic_title FROM forum_topics WHERE topic_cat_id IN ('''+ @.param +''')'exec(@.sql)

|||

you want @.param to be comma separated value is that you are asking for ?

well you could do it in c# , vb.net or any language, could you tell me wht are you doing at the moment for that.

its simple just declare one string variable say str

string str = "";

if (str.length == 0)

str = some_value;

else

str += str +", " + some_other_value;

does that makes sense

regards,

satish

|||

Hey Tetris - you're from Poland maybe ? :)

I have:

ALTER PROCEDURE ShowTopics

@.param varchar
AS

Declare @.sql as varchar(max)

SET @.sql='SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN ('''+@.param+'''))'
exec(@.sql)RETURN

All as you wrote. But when I write as @.param "1,2,3" I'm getting only topics (records) where cat_id is "1". If I choose "3,2,1" I will get only from 3rd category.

|||

I wanna add only that this query:

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN (1,2,3)

Returns me all topics from category 1st,2nd and 3rd. So my question is simply - how can I put (and how declare, or I don't know) in place 1,2,3 a parameter @.categories, and how make it works ?

|||

Oh God ...

My mistake was at the begin of stored procedure. There was:

ALTER PROCEDURE ShowTopics
@.param varchar()

Should be
ALTER PROCEDURE ShowTopics
@.param varchar(100)

(for example)

No it works !!!! Thanks Tetris ! Big thanks !

|||I'm glad you got it to work :) Yeh I'm polish but I live in Canada.|||

So I have one more question.

I have 5 checkboxes:
Checkbox1 (Category 1st),Checkbox2 (Category 2nd),Checkbox3 (Category 3rd),Checkbox4 (Category 4th),Checkbox5 (Category 5th).

And as cookie "Forum_kategorie" I have value = "1,3,5".

So I have topics from category 1,3 and 5. Do you know how can I easily checked checkbox 1,3 and 5 and unchecked second and fourth ?

Default value of "@.param" in this parametrized query above will be 1,2,3,4,5. And at first enter to site all five checkboxes will be checked. I will unchecked second and fourth and click the button, which will add/change cookie "Forum_kategorie" to value "1,3,5" and reload site. After that I will see topics from 1,3,5 category, but I have in some way to define which checkbox is un/checked.

Any ideas ?

Dzi?ki za pomoc :-) / Thanks for help :-)

|||If you are re-loading the same page, as long as you have EnableViewState = True for each checkbox, it should remember what it's last selection was when the site re-loads (on post back) Is this the case?|||

As you have a small rabge of values, take a look at using bitwise operators.

Your check box values would be bases on multiples of two: 1,2,4,8,16.

The value of the parameter that you pass into your stored procedure would be the sum of the values of the check boxes.

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id & @.Categories = forum_kategorie.forum_kat_id)