Dynamic SQL to use columns as parameter. ISNULL() function isn't working anymore

Question

I am trying to use a stored procedure, which contains parameters, which should be used as column names. So for example something like this

Select * from table1 where @columnparameter = 'test'

One approach I've found to realize it, was to use a dynamic sql and so far it is working. But I also want to use the ISNULL function, to check if another parameter is null and if it is, it should return all values of the specified column

Previously (before I was using the dynamic sql statement) I used this lines

[...]
AND (table1.userId = ISNULL(@userId, table1.userId))
AND (table1.fileId = ISNULL(@fileId, table1.fileId))

and this worked fine for me, but since I am using the dynamic sql, I am not getting any results anymore, when I am executing the stored procedure. I have found out, that I get at least results, when I am removing the "ISNULL"-Lines

Right now, the statements are like this:

'AND (q.userId = ISNULL('+@l_userId+', q.userId))
AND (q.fileId = ISNULL('+@l_docId+', q.fileId))'

And in this case I don't get any results at all. It seems, that the sql command isn't executed at all.

But I also tried:

'AND (q.userId = '+ISNULL(@l_userId, 'q.userId')+')
AND (q.userId ='+ISNULL(@l_docId, 'q.fileId')+')'

In this case, I get at least results, when the parameters are null, but as soon as they have a value, I get the error message, that the value of the parameter is an invalid column name...

I also tried some different approaches, but all were misleading and caused various exceptions etc.

For me it's quite confusing working with dynamic sql because of the armada of quotation marks :D

What datatype are @l_userId and @l_docId? If some numeric - cast them into nvarchar. — gofr1
– gofr1, Commented Jun 21, 2016 at 7:51
The behaviour of ISNULL doesn't change. The problem is (almost always) that you construct a query with string concatenation and get it wrong. Mixing up both parameters and dynamic sql is ... strange to say the least. Why don't you simply use parameterised queries? And what is the point of using ISNULL on a parameter? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2016 at 7:52
Are you try to create a "magic" stored procedure with "optional" parameters? What you do is guaranteed to be very slow - by applying a function to a field you prevent the use of any indexes. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2016 at 7:55
Also, execution plans are calculated and cached on the first execution of a stored procedure. This means that your stored procedure will end up with an inappropriate execution plan — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2016 at 7:58
Why don't you simply use an ORM and let it generate correct and fast dynamic queries? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2016 at 7:59

Zohar Peled · Accepted Answer · 2016-06-21 07:51:31Z

2

It's hard to say from the data you provided if you could have better results from regular Sql (that is, without creating a dynamic statement), but in my experience, usually it's possible. Just food for thought.

Anyway, to answer your question, try this:

'AND (q.userId = '+ISNULL('''' + @l_userId +'''', 'q.userId')+')
AND (q.userId ='+ISNULL('''' + @l_docId +'''', 'q.fileId')+')'

Taking advantage of the fact that concatenating strings with null values will result as null, the ISNULL function will still do what it needs to do, and the sql would be valid.

answered Jun 21, 2016 at 7:51

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Echelon Over a year ago

Sometimes the solution can be so obvious. :) Like I said, the amount of quotation marks can be so confusing. The funny thing is, I already had another issue at a different position in my statement, which I could fix exactly the same way, like here, but I couldn't come up with the idea to try the same solution in this case :D That worked for me. Thanks a lot.

Dr. Stitch · Accepted Answer · 2016-06-21 07:58:46Z

2

Do this:

'AND (userId = ISNULL('''+@l_userId+''', q.userId))
 AND (fileId = ISNULL('''+@l_docId+''', q.fileId))'

You need to enclosed variable with escape sequence of single quote '''' so in the generated SQL it will look like this:

AND (userId = ISNULL('value_userId', q.userId))
AND (fileId = ISNULL('value_docId', q.fileId))

I don't know which value is really nullable but since this is a query, its better to first write the table column instead of the variable like this:

'AND (userId = ISNULL(q.userId, '''+@l_userId+'''))
 AND (fileId = ISNULL(q.fileId, '''+@l_docId+'''))'

edited Jun 21, 2016 at 7:58

answered Jun 21, 2016 at 7:51

Dr. Stitch

9186 silver badges15 bronze badges

3 Comments

Zohar Peled Over a year ago

''' should be '''' - you need to escape the inner '.

Panagiotis Kanavos Over a year ago

The OP is probably trying to create "optional" filters, ie parameters that don't filter anything if they are null. The problem is that such constructs are extremely slow because they prevent the use of indexes and result in slow execution plans. The solution of course is simply to use an ORM which takes care of all these concerns. "Optional filters" are unfixable

Echelon Over a year ago

That didn't work for me. With this method, I also get no results at all. But the solution of Zohar did. Anyway. Thanks a lot for your suggestion :) EDIT: aah, didn't see the other comments to this post. I think, Zohar is right, that there is missing a quotation mark.

Sean Pearce · Accepted Answer · 2016-06-21 09:05:15Z

0

It would be much more efficient to only build the conditions into the statement if the variables are not null, as in:

DECLARE @SQL NVARCHAR(MAX);

-- The base statement
SET @SQL = 'SELECT * FROM table1 WHERE @columnparameter = ''test''';

-- Only append the extra conditions if the variables are not null
IF @l_userId IS NOT NULL
    SET @SQL += ' AND table1.userId = ' + @l_userId;

IF @l_docId IS NOT NULL
    SET @SQL += ' AND table1.fileId = ' + @l_docId;

answered Jun 21, 2016 at 9:05

Sean Pearce

1,1705 silver badges10 bronze badges

Comments

IVNSTN · Accepted Answer · 2016-06-21 09:15:00Z

0

Use sp_executesql instead. It supports typed arguments:

set @sql = N'...
AND (table1.userId = ISNULL(@userId, table1.userId))
AND (table1.fileId = ISNULL(@fileId, table1.fileId))
'

exec sp_executesql @sql, N'@userId varchar(100), @fileid varchar(100)', @l_userId, @l_docid

less '''''''''''''' and chances for sql injections.

answered Jun 21, 2016 at 9:15

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL to use columns as parameter. ISNULL() function isn't working anymore

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related