Dynamic SQL query with dynamic WHERE clause

Ignition
1

I have a table with a column of project numbers (local IDs) and several columns of different types of metrics. I need a SQL query where both the selected column and the local ID are dynamic and ultimately fed by parameters. The query works, but NOT with the WHERE clause. I get an com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘)’. error.

what am I getting wrong?

DECLARE @sql nvarchar(max) --declare variable
DECLARE @colName nvarchar(max) --declare variable for parameter
DECLARE @localId nvarchar(max) --declare variable for parameter

set @colName = :colNameParameter  --assign value to parameter variable
set @localId = '247'   --assign value to parameter variable
set @sql = 'SELECT '+ @colName +' FROM ISG_MOD_ELEC_PROGRAMS.ProjectData.ProgramMetricIndicator WHERE localId= '+@localId+')' --build query string with parameter*/

exec(@sql) --execute sql query
2

You probably shouldn’t do this. Accepting a column name as a parameter is opening yourself to SQL injection.

5 Likes
3

Is the preferred method scripting instead? Or a non-dynamic query?

4

You’re getting the exception with the where clause, because, you have a ) with no coresponding (.

That being said, I would probably just return the entire row for @localID and then filter it with a lookup. Either expression or Script.

Alternatively, the “supported” way to do this (which is also discouraged) would be to create a query string parameter in the named query.

Generally if you think you need dynamic sql, you should probably take a step back and make sure that the risks you are taking are really worth it and there isn’t another way to achieve the goal the gets you the same or similar results without the risk.

3 Likes