how to pass Comparison Operators as parameter into a query?

Question

Post reply

how to pass Comparison Operators as parameter into a query?

obarahmeh

SSCommitted

Points: 1595
More actions
May 4, 2009 at 5:14 am

#208956

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Please don't cross-post. Check the other post you did. http://www.sqlservercentral.com/Forums/Topic709311-391-1.aspx

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 2

Kishore.P

SSCrazy Eights

Points: 8147

May 4, 2009 at 6:11 am

#988047

use dynamic query, for more info see SQL BOL.

obarahmeh SSCommitted Points: 1595 More actions · Answer 3

Where can I see SQL BOL?

could you please tell me the way to use a dynamic query to solve my problem here???

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 4

try like the following

declare@sqlquerynvarchar (1000)

,@SQLOperatornvarchar (10)

,@DateTimedatetime

set@SQLOperator= '>='

set@DateTime= getdate ()

set@sqlquery= 'select * from sysobjects where crdate ' + @SQLOperator + ' ''' + convert (varchar (10), @DateTime, 101) + ''' '

print(@SQLQuery)

exec(@SQLQuery)

go

dhulme Mr or Mrs. 500 Points: 570 More actions · Answer 5

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,

Simple: Pass the @Operator as a parameter, too.

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

(

case when @Operator = 'GTE' THEN T.VOLUME_TRADED ELSE 1 END >=

case when @Operator = 'GTE' THEN @Volume ELSE 1 END

)

AND

(

case when @Operator = 'LTE' THEN T.VOLUME_TRADED ELSE 1 END <=

case when @Operator = 'LTE' THEN @Volume ELSE 1 END

)

AND (T.TICKET_NUMBER IS NOT NULL)

AND (O.SYMBOL_CODE= @Symbol)

ORDER BY ActTime DESC