October 27, 2020 at 6:45 pm
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
October 27, 2020 at 7:38 pm
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'
October 27, 2020 at 7:45 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 27, 2020 at 8:52 pm
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
October 27, 2020 at 8:59 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 27, 2020 at 10:00 pm
okay thanks
October 27, 2020 at 10:09 pm
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.
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