Kentico Repeater with Custom Query

Question

OK Here we go.
Using Kentico 11/Portal Engine (no hot fixes)

Have a table that holds Content only page Types. One field of importance is a Date and time field.

I am trying to get rows out of this table that match a certain month and year criteria. For instance give me all records where Month=2 and Year=2018. These argument will be passed via the query string

I have a custom Stored proc that I would like to receive two int(or string) arguments then return a collection of all matching rows.


I am using a RepeaterWithCustomQuery to call the procedure and handle the resulting rows. As you can see below the querystring arguments are named "year" and "monthnumber".

The Query

Me.PR.PREDetailSelect

When my Webpart is set up in this configuration I get the following error:

In my Query, I have tried:

EXEC Proc_Custom_PRDetails @MonthNumber = ##ORDERBY##; @Year = ##WHERE##<br/>
EXEC Proc_Custom_PRDetails @MonthNumber = ##ORDERBY##, @Year = ##WHERE##<br/>
EXEC Proc_Custom_PRDetails @MonthNumber = ##ORDERBY## @Year = ##WHERE##<br/>

Any help would be appreciated (Thanks in advance Brendan). Lastly, don't get too caught up in the names of specific objects as I tried to change names to protect the innocent.

Answer 1

Those macros for queries are not meant to be used with stor procs. The system generates this false condition 1=1 in case if you don't pass anything so it won't break the sql statement like the one below:

SELECT ##TOPN## ##COLUMNS##
FROM View_CMS_Tree_Joined AS V
INNER JOIN CONTENT_MenuItem AS C
ON V.DocumentForeignKeyValue = C.MenuItemID AND V.ClassName = N'CMS.MenuItem'
WHERE ##WHERE##
ORDER BY ##ORDERBY##

You need to convert you stor proc to SQL statement then you can use these SQL macros or use stor proc without parameters

If look at the query above top and where are not good because system will do adjustment, but you can use order by and columns, but they both must be present (I think it passes them as is):

exec proc_test ##ORDERBY##, ##COLUMNS##

Honestly I would advice against doing this, plus you won't gain much by calling stor proc.