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
-
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
Would you like an ugly option? How about:
CREATE FUNCTION DoFunction
(
@Op1 BIGINT
,
@Oper NVARCHAR(2)
,
@Op2 BIGINT
)
RETURNS BIT
AS
BEGIN
IF ( @Oper = '>=' AND @Op1 >= @Op2 ) RETURN 1ELSE IF ( @Oper = '<=' AND @Op1 <= @Op2 ) RETURN 1-- More
RETURN 0END
select * from test where dbo.dofunction ( VOLUME_TRADED , '<=' , @Volume )=1
Otherwise, I might suggest:
SELECT * FROM test WHERE CASE WHEN @Oper='<=' AND VOLUME_TRADED <= @Volume THEN 1 WHEN @Oper='>=' AND VOLUME_TRADED >= @Volume THEN 1 ELSE 0 END = 1