how to pass Comparison Operators as parameter to query in C#?
-
Dear All, I have a query that has some parameters: ( SELECT DISTINCT T.TICKET_NUMBER AS TicketNum, A.DESCRIPTION AS Action, O.SYMBOL_CODE AS Symbol, T.TRADE_PRICE AS Price, T.VOLUME_TRADED AS FillVol, T.EXTENDED_PRICE AS TotalValue, T.SUBMITTED_TIME AS ActTime, T.SUBMITTED_DATE AS TransDate FROM TSDETL AS T INNER JOIN TSORDR AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE AND O.TICKET_NUMBER = T.TICKET_NUMBER INNER JOIN TSORDA AS A ON O.ORDER_ACTION = A.ACTION_CODE WHERE (T.SUBMITTED_DATE = @SUBMIT_DATE) AND (T.VOLUME_TRADED >= @Volume) AND (T.TICKET_NUMBER IS NOT NULL) AND (O.SYMBOL_CODE= @Symbol) ORDER BY ActTime DESC ) I passed the three parameters (@SUBMIT_DATE, @Volume, @Symbol) successfully, but I want to read the operator (>= or <=) as a parameter...I have a combo box with >= and <=, and I want to pass the value of this combo into the query. How can I do this??? this is because the operator may be >= or <=, and I don't want to write two queries.
Kind Regards OBarahmeh
-
Dear All, I have a query that has some parameters: ( SELECT DISTINCT T.TICKET_NUMBER AS TicketNum, A.DESCRIPTION AS Action, O.SYMBOL_CODE AS Symbol, T.TRADE_PRICE AS Price, T.VOLUME_TRADED AS FillVol, T.EXTENDED_PRICE AS TotalValue, T.SUBMITTED_TIME AS ActTime, T.SUBMITTED_DATE AS TransDate FROM TSDETL AS T INNER JOIN TSORDR AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE AND O.TICKET_NUMBER = T.TICKET_NUMBER INNER JOIN TSORDA AS A ON O.ORDER_ACTION = A.ACTION_CODE WHERE (T.SUBMITTED_DATE = @SUBMIT_DATE) AND (T.VOLUME_TRADED >= @Volume) AND (T.TICKET_NUMBER IS NOT NULL) AND (O.SYMBOL_CODE= @Symbol) ORDER BY ActTime DESC ) I passed the three parameters (@SUBMIT_DATE, @Volume, @Symbol) successfully, but I want to read the operator (>= or <=) as a parameter...I have a combo box with >= and <=, and I want to pass the value of this combo into the query. How can I do this??? this is because the operator may be >= or <=, and I don't want to write two queries.
Kind Regards OBarahmeh
Add a parameter of type
BIT, and set it to "0" when you want to perform a "<=" and set it to "1" whenever you want to perform a ">=". You can than add anIF-ELSE-ENDIFconstruction to differentiate between the two queries;IF @MyBIT = 0 THEN
BEGIN
SELECT [..]
WHERE >=
END
ELSE
BEGIN
SELECT [..]
WHERE <=
ENDEnjoy :)
I are troll :)
-
Add a parameter of type
BIT, and set it to "0" when you want to perform a "<=" and set it to "1" whenever you want to perform a ">=". You can than add anIF-ELSE-ENDIFconstruction to differentiate between the two queries;IF @MyBIT = 0 THEN
BEGIN
SELECT [..]
WHERE >=
END
ELSE
BEGIN
SELECT [..]
WHERE <=
ENDEnjoy :)
I are troll :)
sorry for posting the same question in two places. I tried your solution as the following: declare @operator bit; set @operator = 1; ( SELECT DISTINCT T.TICKET_NUMBER AS TicketNum, A.DESCRIPTION AS Action, O.SYMBOL_CODE AS Symbol, T.TRADE_PRICE AS Price, T.VOLUME_TRADED AS FillVol, T.EXTENDED_PRICE AS TotalValue, T.SUBMITTED_TIME AS ActTime, T.SUBMITTED_DATE AS TransDate FROM TSDETL AS T INNER JOIN TSORDR AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE AND O.TICKET_NUMBER = T.TICKET_NUMBER INNER JOIN TSORDA AS A ON O.ORDER_ACTION = A.ACTION_CODE WHERE (T.SUBMITTED_DATE = '20090405') AND (CASE WHEN @operator = 1 THEN T.VOLUME_TRADED >= 500 ELSE T.VOLUME_TRADED <= 500 END) AND (T.TICKET_NUMBER IS NOT NULL) AND (O.SYMBOL_CODE= 'BOP') ORDER BY ActTime DESC ) but this did not work , it gives me an error : Msg 102, Level 15, State 1, Line 10 Incorrect syntax near '>'.
Kind Regards OBarahmeh
-
sorry for posting the same question in two places. I tried your solution as the following: declare @operator bit; set @operator = 1; ( SELECT DISTINCT T.TICKET_NUMBER AS TicketNum, A.DESCRIPTION AS Action, O.SYMBOL_CODE AS Symbol, T.TRADE_PRICE AS Price, T.VOLUME_TRADED AS FillVol, T.EXTENDED_PRICE AS TotalValue, T.SUBMITTED_TIME AS ActTime, T.SUBMITTED_DATE AS TransDate FROM TSDETL AS T INNER JOIN TSORDR AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE AND O.TICKET_NUMBER = T.TICKET_NUMBER INNER JOIN TSORDA AS A ON O.ORDER_ACTION = A.ACTION_CODE WHERE (T.SUBMITTED_DATE = '20090405') AND (CASE WHEN @operator = 1 THEN T.VOLUME_TRADED >= 500 ELSE T.VOLUME_TRADED <= 500 END) AND (T.TICKET_NUMBER IS NOT NULL) AND (O.SYMBOL_CODE= 'BOP') ORDER BY ActTime DESC ) but this did not work , it gives me an error : Msg 102, Level 15, State 1, Line 10 Incorrect syntax near '>'.
Kind Regards OBarahmeh
My apologies, I didn't mean to refer to a
CASEstatement. What you're looking for is a simpelerIFstatement;DECLARE @operator BIT;
SET @operator = 1;IF @operator = 1 THEN
BEGIN
SELECT DISTINCT
T.TICKET_NUMBER AS TicketNum,
A.DESCRIPTION AS Action,
O.SYMBOL_CODE AS Symbol,
T.TRADE_PRICE AS Price,
T.VOLUME_TRADED AS FillVol,
T.EXTENDED_PRICE AS TotalValue,
T.SUBMITTED_TIME AS ActTime,
T.SUBMITTED_DATE AS TransDate
FROM TSDETL AS T
INNER JOIN TSORDR
AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE
AND O.TICKET_NUMBER = T.TICKET_NUMBER
INNER JOIN TSORDA
AS A ON O.ORDER_ACTION = A.ACTION_CODE
WHERE (T.SUBMITTED_DATE = @SUBMIT_DATE)
AND (T.VOLUME_TRADED >= @Volume)
AND (T.TICKET_NUMBER IS NOT NULL)
AND (O.SYMBOL_CODE= @Symbol)
ORDER BY ActTime DESC
END
ELSE
BEGIN
SELECT DISTINCT
T.TICKET_NUMBER AS TicketNum,
A.DESCRIPTION AS Action,
O.SYMBOL_CODE AS Symbol,
T.TRADE_PRICE AS Price,
T.VOLUME_TRADED AS FillVol,
T.EXTENDED_PRICE AS TotalValue,
T.SUBMITTED_TIME AS ActTime,
T.SUBMITTED_DATE AS TransDate
FROM TSDETL AS T
INNER JOIN TSORDR
AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE
AND O.TICKET_NUMBER = T.TICKET_NUMBER
INNER JOIN TSORDA
AS A ON O.ORDER_ACTION = A.ACTION_CODE
WHERE (T.SUBMITTED_DATE = @SUBMIT_DATE)
AND (T.VOLUME_TRADED <= @Volume)
AND (T.TICKET_NUMBER IS NOT NULL)
AND (O.SYMBOL_CODE= @Symbol)
ORDER BY ActTime DESC
ENDYou can make this stored procedure even simpeler by splitting the query into; * a view that does the selection of the fields and the joins * put the filter- and order-statements in a select-query that you run against the aforementioned view :) I hope this helps :thumbsup:
I are troll :)
-
My apologies, I didn't mean to refer to a
CASEstatement. What you're looking for is a simpelerIFstatement;DECLARE @operator BIT;
SET @operator = 1;IF @operator = 1 THEN
BEGIN
SELECT DISTINCT
T.TICKET_NUMBER AS TicketNum,
A.DESCRIPTION AS Action,
O.SYMBOL_CODE AS Symbol,
T.TRADE_PRICE AS Price,
T.VOLUME_TRADED AS FillVol,
T.EXTENDED_PRICE AS TotalValue,
T.SUBMITTED_TIME AS ActTime,
T.SUBMITTED_DATE AS TransDate
FROM TSDETL AS T
INNER JOIN TSORDR
AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE
AND O.TICKET_NUMBER = T.TICKET_NUMBER
INNER JOIN TSORDA
AS A ON O.ORDER_ACTION = A.ACTION_CODE
WHERE (T.SUBMITTED_DATE = @SUBMIT_DATE)
AND (T.VOLUME_TRADED >= @Volume)
AND (T.TICKET_NUMBER IS NOT NULL)
AND (O.SYMBOL_CODE= @Symbol)
ORDER BY ActTime DESC
END
ELSE
BEGIN
SELECT DISTINCT
T.TICKET_NUMBER AS TicketNum,
A.DESCRIPTION AS Action,
O.SYMBOL_CODE AS Symbol,
T.TRADE_PRICE AS Price,
T.VOLUME_TRADED AS FillVol,
T.EXTENDED_PRICE AS TotalValue,
T.SUBMITTED_TIME AS ActTime,
T.SUBMITTED_DATE AS TransDate
FROM TSDETL AS T
INNER JOIN TSORDR
AS O ON O.SUBMITTED_DATE = T.SUBMITTED_DATE
AND O.TICKET_NUMBER = T.TICKET_NUMBER
INNER JOIN TSORDA
AS A ON O.ORDER_ACTION = A.ACTION_CODE
WHERE (T.SUBMITTED_DATE = @SUBMIT_DATE)
AND (T.VOLUME_TRADED <= @Volume)
AND (T.TICKET_NUMBER IS NOT NULL)
AND (O.SYMBOL_CODE= @Symbol)
ORDER BY ActTime DESC
ENDYou can make this stored procedure even simpeler by splitting the query into; * a view that does the selection of the fields and the joins * put the filter- and order-statements in a select-query that you run against the aforementioned view :) I hope this helps :thumbsup:
I are troll :)