I just answered this question today for another DNN developer that works with XMod Pro (XMP) so I went back looking for an old forum post I'd made on the topic. Here's what I posted earlier. Basically it's all about returning a message to the user, whether you do it with a SQL stored procedure Output or Error value... both work:

If you have an XMP Form where the submit action does an insert to create a new record, then you are able to setup, instead, a SQL Stored Procedure for the submit statement. In this stored procedure you will have it first check to see whether that record exists (compare a key data element) and if it exists, the stored procedure would not insert the new record, and instead have an Output or Error value. That value can then be returned to the screen for the user to see "Error, duplicate entry exists" etc.

If you don't already have a copy of the Help document, be sure to download it from the Downloads section of the dnndev.com site. I recommend the CHM version but you can also look through the PDF version as well.

Info for this topic is found in the Form Controls section, under AddForm / EditForm

Here's a little bit of that which applies to this case, especially the Error part:

Displaying the return value and/or Output parameter value from a stored procedure (new to v.4): If your form's SubmitCommand calls a stored procedure that returns a value or sets the value of an output parameter, you can display or otherwise use that value in your success template

Add parameters for your Output and/or Return Value. Be sure to set the Direction property accordingly:

<SubmitCommand CommandText="addContact" CommandType="StoredProcedure">
	<Parameter Name="FirstName" />
	<Parameter Name="LastName" />
	<Parameter Name="retVal" Direction="ReturnValue" />
	<Parameter Name="newID" Direction="Output" DataType="int32" />
</SubmitCommand>

NOTES: Your parameter name(s) should be unique among all your form's controls (i.e. their DataField properties). Also, if you set a DataType for your return value/output parameter, ensure that the returned value returned matches it. Otherwise an error will be thrown. Also, if you're passing textual data types like varchar or nvarchar, please ensure that your <Parameter> tags specify a Size property - otherwise, only the 1st character will be returned in an output parameter.

If your stored procedure will pass back friendly error messages (like duplicate record messages), set that up. Your stored procedure must have an OUTPUT parameter whose name is @ERROR (all caps). You can handle this by adding a ERROR parameter and adding a <Validate Type="Database" /> and a <ValidationSummary /> tag, if your form doesn't already have one. Any errors from the database will be displayed in the <ValidationSummary> tag.

<SubmitCommand CommandText="addContact" CommandType="StoredProcedure">
	<Parameter Name="FirstName" />
	<Parameter Name="LastName" />
	<Parameter Name="retVal" Direction="ReturnValue" />
	<Parameter Name="newID" Direction="Output" DataType="int32" />
	<Parameter Name="ERROR" Direction="Output" DataType="string" />
</SubmitCommand>
	...
<AddButton Text="Add" />
<Validate Type="Database" />
<ValidationSummary />

MooreCreative XMOD Development Blog

In the MooreCreative XMOD PRO Development Blog, I'll try to share some of the thoughts and ideas that we've come up with on a day-to-day basis as my development company uses DNN + XMOD PRO to tackle client projects. I'll include items such as tips/tricks, did-you-know articles, troubleshooting/testing examples as well as posting samples solutions and explanations behind our experience working with XMOD and jQuery functions, PageBlaster, ZLDNN Article, and other modules/elements.

If you would like to see us tackle a topic, especially anything out of the forums that could use further examples, please, don't hesitate to email me, and I'll look into preparing it for a blog post.