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!
Currently:
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 usp_DisplayReport and just redisplay the relevant section (with suitable Hints / Wrapper for Javascript to make the substituion)
CREATE PROCEDURE usp_OrderHeaderSave
... Save the record ...
IF @AJAX = 1
BEGIN
EXEC usp_DisplayReport @OrderID = @OrderID, @DisplayResultSet = 1, @AJAX = 1
END
and usp_DisplayReport then just outputs the ResulSet for the Order Header - with suitable Wrappers so that the Javascript AJAX routines can "inject" that content into the right part of the page.
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
BEGIN
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
END
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?