Basic SQL statement returning error

Question

I am new to SQL and I keep getting an error with the following SQL statement:

SELECT Id,Name
FROM {PositionInCompany}
WHERE @AdvancedFilter

At the moment I am just testing the default case for AdvancedFilter, so it is set to"1=1" and should return all the names and ids from PositionInCompany. This works if I simply put "1=1" in, but not if I use the variable (which has exactly the same string value).

The error is attached. What am I missing?

Cole

The purpose of variables or parameters is to contain data *values*. Not arbitrary chunks of *code*. Are you familiar with any language where, if you have a string variable , `t` containing the value `A == B`, you're allowed to write `if(t)` and it will evaluate whether `A` and `B` are equal? Most languages I'm familiar with don't let you do that and T-SQL is unremarkable in this regard. — Damien_The_Unbeliever, May 24 '18 at 07:52
**[SELECT * FROM tbl WHERE @condition](http://www.sommarskog.se/dynamic_sql.html#Condition)** `" Just forget it. If you are doing this, you have not completed the transition to use stored procedure and you are still assembling your SQL code in the client."` Plus it looks like table name is templated. Really bad idea to write code like that. — Lukasz Szozda, May 24 '18 at 07:54
That error doesn't match your query. Please include the _exact_ error and the _exact_ query. — Jonathan Hall, May 24 '18 at 07:55
Also, don't use screen shots of errors or text--just copy and paste the text directly into the question. — Jonathan Hall, May 24 '18 at 07:55

score 1 · Answer 1 · answered May 24 '18 at 07:57

This is probably because as you said it is a string which will translate to :

SELECT Id,Name FROM {PositionInCompany} WHERE '1=1'

sql would read this as just a string and it wont know it is something it can excute. You could change the value of AdvancedFilter to something else

@AdvancedFilter = 'test'
SELECT Id,Name FROM {PositionInCompany} WHERE 'test'=@AdvancedFilter

or use something to dynamically build the query

score 1 · Answer 2 · answered May 24 '18 at 07:58

Parameters in SQL are placeholders for data. If they contain SQL code, that code is ignored by the database and is treated as data.
This is why you can't send SQL chunks as parameter, and that's also the reason why you can't parameterize identifiers.

If you need a dynamic where clause, you need to use dynamic SQL, but that usually have a cost in both performance and security.

However, I suspect your current SQL is wrong in the first place, assuming FROM {PositionInCompany} means you are concatenating the table name into the sql string (only assuming, since you didn't provide any information to show that).

If my assumption is correct, you need to stop doing that, read about SQL injection and how parameterized queries protects you from it, and understand that this form of concatenation is also vulnerable to SQL injection attacks.

In SQL, don't look for shortcuts. Write specific queries for specific tables. That's the only safe way to do it, and 99.9% of the time it has the best performance.

score 0 · Answer 3 · answered May 25 '18 at 08:27

Thank you for your responses.

I'm doing this in Outsystems, but I assumed it was a simple SQL problem. However, there was simply an "Expand Inline" option that needed to be selected in order to insert a string statement. It works fine now.

regards, Cole

Basic SQL statement returning error

3 Answers3