Using operators as variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using operators as variables

Author

Topic

fnsmedia
Starting Member

14 Posts

Posted - 2003-06-30 : 09:30:41

Is it possible to use SQL operators as variables?

I have created an asp page with a recordset that I wish to determine whether a set of orders between a date range are either carried out or cancelled.

i.e where orderstatus <> cancelled or = cancelled

Can I create a variable that will use either the equal or not equal operator?

Nazim
A custom title

1408 Posts

Posted - 2003-06-30 : 09:47:28

Dynamic Sql is what the Doctor has ordered you.
Check these Articles from our Sql Guru Merkin.

http://sqlteam.com/item.asp?ItemID=4599
http://sqlteam.com/item.asp?ItemID=4619

eg:

declare @sql varchar(100)
declare @operator char(1)
set @operator='='
set @sql='select * from authors where au_id' +@operator+'''172-32-1176'''
exec (@sql)

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

fnsmedia
Starting Member

14 Posts

Posted - 2003-06-30 : 10:09:09

cheers

will have a pop at that

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-30 : 10:29:24

Dynamic SQL is an option, but is unfortunately prone to security and performance problems. You can do exactly the same thing using a little logic nicely wrapped inside a stored procedure.

CREATE PROCEDURE GetOrders
@StartDate DATETIME, @EndDate DATETIME, @Cancelled BIT
AS
BEGIN
--SET @Cancelled to 0 for all non-cancelled orders, 1 for cancelled

SELECT Col1, Col2, Col3, Col4 FROM tblOrders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
AND ((@Cancelled = 0 AND OrderStatus <> 'Cancelled')
OR (@Cancelled = 1 AND OrderStatus = 'Cancelled'))

END

I would prefer, though, that you have a table for Order statuses, so you can simply pass a StatusID into the procedure and return all orders with that status.

Owais

Edited by - mohdowais on 06/30/2003 10:32:41

fnsmedia
Starting Member

14 Posts

Posted - 2003-07-02 : 06:25:57

I am already passing the Status ID thru. I have up to 12 diff status available and only need to query whether = or <> when statusid is 12.

Will look into your other comments
THanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-02 : 08:46:07

I think I hear you:

something like:

select data.*,
CASE when statusID = 12 THEN 'Y' ELSE 'N' END as [Cancelled?]
from
yourdata

???

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources