Incorrect syntax near '%' error displaying when Stored Procedure Parameter is given to repeater with

Novice User asked on November 5, 2020 02:25

I am trying to use a query in a repeater with a custom query. I am using query option and writing this as my query

exec Proc_CMS_GetMyRows {% CurrentUser.UserName @%}

The above statement result in an error Incorrect syntax near '%' while if I use hard coded values it works exec Proc_CMS_GetMyRows 'myusername'

What am I doing wrong here ?

repeater with custom quer

Correct Answer

Dmitry Bastron answered on November 5, 2020 17:41

Ok, Kentico must be checking where condition to be valid then. Right, you can trick it like that by using ##COLUMNS## then. Put this into Query:

exec Proc_CMS_GetMyRows ##COLUMNS##

And this into Selected columns macro (single quotes are important!):

'{% CurrentUser.UserName #%}'

But all in all, it's a bit of hacking the system. It's not designed to support macro in queries. And ideally, if you can switch your query from calling a stored procedure to actual query where you can put ##WHERE## condition that would be the right way of doing things.

1 votesVote for this answer Unmark Correct answer

Recent Answers

Dmitry Bastron answered on November 5, 2020 12:09

Hi Novice User,

Unfortunately, macro expressions are not supported inside queries, please refer to this documentation link (and scroll to the bottom of the page):

Note: Macros are only supported in web part properties. You cannot place macros directly into the SQL code of queries or stored procedures.

So, what you can do is to use ##WHERE## condition in your query:

exec Proc_CMS_GetMyRows ##WHERE##

and add {% CurrentUser.UserName %} macro in where condition of your web part.

1 votesVote for this answer Mark as a Correct answer

Novice User answered on November 5, 2020 17:22 (last edited on November 5, 2020 17:36)

Hi Dmitry,

It tried the changes you suggested and it resulted in an error which is Message: Invalid SQL query in property "wherecondition".

I added {%CurrentUser.UserName |(handlesqlinjection)false #%} as well.

These are the changes I did.
Capture

0 votesVote for this answer Mark as a Correct answer

Novice User answered on November 5, 2020 18:07 (last edited on November 5, 2020 18:08)

Hi Dmitry,

Thank you so much. Yes ##COLUMNS## worked like a breeze. FWIW: I had tried pasting my stored procedure text inside the Query Text with the where clause and replacing @myparaname with ##WHERE## in the query. But I still was displaying error for me, so I dropped that idea. Message: Invalid SQL query in property "wherecondition".

Thanks again!

0 votesVote for this answer Mark as a Correct answer