I am looking for an example of how to add communicate in a modified Stored Procedure within a DNN program, my need store-admin/pageid/productadmin/productid/0 but any program ok. 

I have the sql that testing works and it bypasses the insert of adding the record.

How do I tell DNN to stop the add record and give a message to the user?

The sql is in direct sql mode:

       if 'ContractCellPhoneMktPlx_02' not in (select ModelNumber from Store_Products)

       BEGIN

       INSERT INTO dbo.Store_Products

              (PortalID, CategoryID,  ModelNumber, UnitCost,  ProductWeight, ProductHeight, ProductLength, ProductWidth, SaleStartDate, SaleEndDate, SalePrice, StockQuantity, LowThreshold, HighThreshold, DeliveryTime, PurchasePrice, RoleID, Virtual, VirtualFileID, AllowedDownloads)

              VALUES

              (1, 1, 'ContractCellPhoneMktPlx_02',  1.99, 10.52, 0, 0, 0, 6/15/2014, 6/16/2014, 9.99, 0, 0, 0, 1, 5.99, 1, 0, 0, 0)

              SELECT SCOPE_IDENTITY()

       END

       ELSE

       SELECT 'Model Number already in database' as 'error'

But it throws an error if it detects duplicate ModelNumber & ok if no error

Error: Store Admin is currently unavailable. DotNetNuke.Services.Exceptions.ModuleLoadException: Input string was not in a correct format. ---> System.FormatException: ….

Thanks in advance for any help!!!

The problem you have is: The API is expecting an integer from the SP, but in case of an error the procedure returns a string.

You can solve that by returning some value that would not be produced, like -1, and then reacting on that value in the API.

To produce an error inside an SQL statement you should do something like this:

BEGIN TRY
   BEGIN TRANSACTION
   IF 'ContractCellPhoneMktPlx_02' not in (select ModelNumber from Store_Products)
      BEGIN
         INSERT ....
         SELECT SCOPE_IDENTITY()
         COMMIT TRANSACTION
      END
   ELSE
      BEGIN
         RAISERROR('Model Number already in database', 16, 1)
      END
END TRY

BEGIN CATCH
   ROLLBACK TRANSACTION
   DECLARE @ErrorMessage nvarchar(max), @ErrSeverity int
   SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
   RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Best wishes

Michael