Trying to get a arithmetic operation from an Variable

Question

Post reply

Trying to get a arithmetic operation from an Variable

itmasterw 60042

SSCrazy

Points: 2858
More actions
October 27, 2020 at 6:45 pm

#3801812
Hi,
I am not sure if this can be done, but I seem to remember there is some format thing that can be done with quates.
Thant would pull out the value so that I can use it in something like this:
```
Declare @dd Varchar(2) = '<' 
SELECT *FROM SalesWhere [sale_id] + @dd + 2
```
But nothing I have doe works as this naturally doesn't as it is any way?
any ideas would be appreciated.
Thank you
- This topic was modified 4 years, 2 months ago by itmasterw 60042.
- This topic was modified 4 years, 2 months ago by itmasterw 60042.

November 12, 2020 at 10:37 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 1

I get close when I do this, but still does not work.

Declare @dd Varchar(22) ='<'
Declare @Where  Varchar(22)

SET @Where = ' ''' + @dd + ''' '

SELECT @Where

SELECT * 
FROM Sales
Where [sale_id] = [sale_id] +  @Where +  '7'

Phil Parkin SSC Guru Points: 246988 More actions · Answer 2

You'll have to use fully dynamic SQL to make this work.

Building arithmetic expressions like this is generally regarded as an anti-pattern in the T-SQL world. What is the underlying business requirement? Maybe there is a better way.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 3

In the application they would select, from a drop down one of the symbols (<,>,=,!=). I figured I can assigning them letters or numbers, if it is number one for example, they would be selecting "<".

My query is in a stored procedure that is fairly huge, but the concept is the same as the example I gave.

Thank you

Phil Parkin SSC Guru Points: 246988 More actions · Answer 4

itmasterw 60042 wrote:

In the application they would select, from a drop down one of the symbols (<,>,=,!=). I figured I can assigning them letters or numbers, if it is number one for example, they would be selecting "<".
My query is in a stored procedure that is fairly huge, but the concept is the same as the example I gave.
Thank you

As I mentioned, dynamic SQL is the only way forward for you. It's not going to be pleasant to maintain and you'll need to protect against SQL Injection.

You haven't really provided enough information for anyone to suggest an alternative.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 5

itmasterw 60042

SSCrazy

Points: 2858

October 27, 2020 at 10:00 pm

#3801893

okay thanks

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 6

As a thought - if you can translate the symbols to numeric values, you could do it with some messy WHERE clause. What I mean is something like:

DECLARE @inputSymbol CHAR(10) = '<'
DECLARE @intSymbol INT
SELECT @intSymbol = CASE WHEN @inputSymbol = '<' THEN 1 WHEN @inputSymbol = '>' THEN 2 ELSE 0 END
SELECT *
FROM TABLLE
WHERE Sale_ID < CASE WHEN @intSymbol=1 THEN 7 ELSE (Sale_ID + 1) END
AND Sale_ID > CASE WHEN @intSymbol=2 THEN 7 ELSE (Sale_ID - 1) END;

Basically, we are building a set of cases we want to evaluate to TRUE when the symbol doesn't match the input symbol and otherwise we compare it to the known value you are looking for. You'd need 1 WHERE per symbol type.

In the above example, if the symbol is <, then the first line of the WHERE evlautates to "Sales_ID < 7" and the second line evaluates to "AND Sales_ID > (Sales_ID - 1)" and A > A-1 will always be true. Depending on the number of symbols you have, this could get quite lengthy and I am pretty sure that CASE statements in a WHERE are bad for performance.

ALTERNATELY, you could have 4 different SELECT statements and an IF ELSE IF blocks where you check the character to see if it matches the symbol you expect. Again, this gets messy as you need to update the SQL code every time you add a new symbol.

As Phil said, dynamic SQL will be the easiest way to handle this and it will handle symbols being added in the application layer without needing to change the SQL layer (as long as the symbols are valid TSQL). But it has the risk of SQL injection.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.