SQL Procedure - Using a parameter as an operator

Question

I have a procedure which I am trying to get to select the results based on the value of a measure being above a stated parameter. However, I would like the greater than to be changable to equals or less than.

I have tried substituting the > for a parameter, but it gives me the error: sql syntax error: incorrect syntax near "PARAM_OP" (Where PARAM_OP is the name of the parameter which should be substituted for ther operator.

Any ideas would be welcome.

Code below:

var_out = 
SELECT
    "Col1" AS "Col1",
    "Col2" AS "Col2",
    "Col3" AS "Col3",
    SUM(Col4) AS "Col4"

FROM <schema>.<view>
WHERE "Col2" = PARAM_1
GROUP BY Col1, Col2, Col3
HAVING SUM(Col4) > PARAM_2
ORDER BY SUM(Col4);

Does one of your lines read: HAVING SUM(Col4) PARAM_OP PARAM_2 If so you probably want to use dynamic SQL. — Aushin, Jul 31 '12 at 13:25
You can't use parameters for structural parts of SQL statements - so not for any keyword, operator, or identifier. Parameters can appear only where you'd expect a value expression. IOW Parameters are placeholders for values that get bound to a "real" value before query time. The solution is to generate the SQL statement in your application code, and execute that generated statement. See also http://stackoverflow.com/questions/11518315/classic-asp-sql-parameterization-how-can-i-remove-single-quotes-from-a-paramet — Roland Bouman, Jul 31 '12 at 13:29
Aushin - How would this be created in Dynamic SQL? Roland / Holder - I'm using a Hana database, so I would rather have all the code generated serverside to utilise the processing power. — user1565766, Jul 31 '12 at 13:42

score 0 · Answer 1 · answered Jul 31 '12 at 14:00

I'm imagining something like.

DECLARE @Code VARCHAR(MAX)
SELECT @Code = 
'
var_out = 
SELECT
    "Col1" AS "Col1",
    "Col2" AS "Col2",
    "Col3" AS "Col3",
    SUM(Col4) AS "Col4"
FROM <schema>.<view>
WHERE "Col2" = ' + PARAM_1 + 
'GROUP BY Col1, Col2, Col3
HAVING SUM(Col4) ' + PARAM_OP + ' ' + PARAM_2 
+ 'ORDER BY SUM(Col4);'


EXEC sp_executesql @Code

So you build a string that looks like the SQL you wanted to execute and then you run the SQL string using whatever means your RDBMS provides for doing that. In MSSQL, that would be sp_executesql

SQL Procedure - Using a parameter as an operator

1 Answers1