Not sure this is right for this forum, but I’ve been unsuccessful in dreaming up keywords to find answers in Google.
We have some CRUD routines. They just Display a form, Get data entry, Save the record to table. Taking care of INSERT for new records and UPDATE for existing, and all the data validation etc.
Basically a marriage of HTML template for the Form and an SProc for the SAVE.
We have an additional “
IsThisAJAX?” parameter. That causes some elements of the Form, suitable for whole-page display, to be suppressed, and so on. So my CRUD works fine either standalone or AJAX.
However, the Launch of the AJAX form normally requires some sort of in-page partial refresh - to reflect the changes made in the CRUD form. I’m looking for Best Practice ways of doing that, as I’m pretty sure that my current method is PANTS!
Lets say that I have
usp_DisplayReport which outputs two resulsets : Order Header and then Order Items.
Lets say I also have
usp_OrderHeaderSave which can be called from an AJAX form allowing update of the Order Header. Once the AJAX form is Saves I then want to redisplay the Order Header using in-page refresh. To achieve this my
usp_OrderHeaderSave has some code to call the
CREATE PROCEDURE usp_OrderHeaderSave
... Save the record ...
IF @AJAX = 1
EXEC usp_DisplayReport @OrderID = @OrderID, @DisplayResultSet = 1, @AJAX = 1
Problem with this is that the CRUD routine has to carry to knowledge of what to call the Caller with. When I need to have the ability to use that same Order Header AJAX Edit Form on, say, half a dozen different reports the in-page replace code, launched from
usp_OrderHeaderSave, gets annoying to maintain. Sure, I can just use a CASE but the code is removed from the “caller” and typically bugs start to creep in and/or I have to modify the
usp_OrderHeaderSaveroutine every time I have a new requirement for an AJAX Order Header Edit ability.
So my thought was to have the caller pass a “hint” so that the
usp_OrderHeaderSave can have a more generic routine for the redisplay part. But I don’t want to pass anything (such as actual dynamic SQL) which might be hijacked by someone unscrupulous!
I don’t know if it is a daft idea, but the only thought I have come up with is a table of “Call back templates”, so my
usp_OrderHeaderSave code could become:
IF @AJAX = 1
SELECT @strSQL = CBT.SQL -- e.g. usp_DisplayReport @OrderID = @OrderID, @DisplayResultSet = 1, @AJAX = 1
FROM CallBackTemplates AS CBT
WHERE CBT.ID = @CallBackID -- Provided by caller - usp_DisplayReport in this case
EXEC sp_ExecuteSQL @strSQL, N'@OrderID int', @OrderID = @OrderID
I’m thinking that the CRUD routine will have a set pf parameters that make sense -
@OrderID in this case - so that probably won’t change with time, and the Caller can provide a template which includes anything else that it, itself, needs - in this case
@DisplayResultSet = 1
But maybe there is a Better Way?