How to use where wiht variable

[topci]

Hi,
i have a "little" problemm. I need to use local variable, but complete condition is in one of sql tables.

For example, i wil write this way:

DECLARE @STRING VARCHAR(30)

SELECT @STRING = (SELECT MYCONDITION FROM CONDITIONTABLE WHERE CONDITION = 1)

-- HERE I HAVE A ANSWARE FROM SQLSERVER
-- @STRING = 'YEAR = 2009'

SELECT *
FROM MYDATATABLE
WHERE @STRING


Any ideas? :)

Tnx, Dat

[ck9663]

Depending on your need, you might be able to use a dynamic query or a table-function.

Happy Coding!!!

--- CK

[topci]

Oh, sory, but i need more information. It's be very helpfull whenn cann you give me some example... In moment i will use a "build" query with exec (query), but if have someone some better idea, i will be "old and happy" :) ...

[ck9663]

Could you explain your requirement a little bit more detail? Are you saying the column on your WHERE condition could vary? Or the value of the variable on the WHERE condition could vary but the columns are fixed?

--- CK

[topci]

Hmmm... I will explane the problem with "psevdo" code (sorry for my Englesh)...

I have sales data in table KUM_SALES (like cumulative_sales). Condition is written in table SALES_COND.
Kum_sales have fields like:
- YEAR
- CUSTOMER_ID
- QUANTITY
- VALUES_ WITHOUT_VAT
- VAT
- VALUES_WITH_VAT

Table SALES_COND have fields:
- TYPE_COND (tipe of condition)
- COND (description of condition)

for exemple, my customer will choice:

TYPE_COND = 1 OR 2 OR 3, ...

IN TYPE OF CONDITION IS WRITEN:

1 = YEAR = 2009 AND CUSTOMER_ID IN ('123','223','333')
2 = YEAR = 2008 AND CUSTOMER_ID IS NOT IN ('123','223','333')
3 = CUSTOMER_ID LIKE '5%'
...

the main quiry is the same for every type of condition:

SELECT YEAR,
CUSTOMER_ID,
SUM(QUANTITY) AS QUIANTITY,
SUM(VAT) AS VAT,
...
FROM KUM_SALES
WHERE ...
-- here need to be (select from a cond_table where type_cond = for example 3)
CUSTOMER_ID LIKE '5%'


I think it cann be solved with SP_EXCECUTEQUIRY, where i cann build quiry... But whenn is posible to geth a simples example, it will be very usefull

Tnx,
Darko

[ck9663]

When you said your customer chooses a condition, are you talking about some kind of GUI/Application that has a drop-down or something like that, that they can use to choose the condition? If YES, then you can build your query dynamically on your front-end.

Happy Coding!!!


--- CK