Update records by passing parameters to a stored procedure

#1

Hello,

I’ve developed a few forms using the data input functionality of Dundas however my users would like the ability to update the records they have input. I’m wondering if anyone has explored the use of passing paramaters to stored procedures in order to update the data input table in the back end of Dundas? e.g.

  1. CREATE PROCEDURE [dbo].[sp_UpdateRecord]

@ProductID int, @Value NVARCHAR(MAX)

AS

BEGIN

UPDATE [dbo].[DundasTable]

SET Column = @Value

WHERE productID = @ProductID

END

Any ideas would be appreciated :slight_smile:

#2

More specifically I’m looking for a way of allowing users to edit records without the user accessing the data cube :slight_smile:

#3

Hello @shane.o.sullivan,

Just for my understanding: do you want to build a stored procedure or you are using the built-in functionality for data imput?

#4

I’m using the built in functionality for data input which works well however I would like to give my users the ability to update records that have been created. I haven’t built a sproc yet but was considering if an update to a record could be achieved using one?

#5

Hello @shane.o.sullivan,

I have the same functionality implemented with a stored procedure. For me, the advantage of the stored procedures is that I have the freedom to do anything inside the dashboard (CRUD operations) and also to have a dedicated table which stores this information (you can do a snapshots of this data etc.)
You can try something like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[test]
@operation int,
@ProductID int,
@Value NVARCHAR(MAX)

AS
BEGIN
SET NOCOUNT ON;

if @operation=1 --update
begin

UPDATE [dbo].[DundasTable]
SET Column = @Value
WHERE productID = @ProductID

end

if @operation=2 --delete
begin

delete 
from [dbo].[DundasTable]
WHERE productID = @ProductID

end
–same for insert
END

https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/using-manual-select-placeholders

#6

The Built in Dundas Functionality to let users submit data is stores that data in the Dundas Database.
If you want more than what the built in ability of Dundas has then you will have to make table out side of the Dundas Database to store that data and use the procedure method that Costin posted.
If I am wrong please let me know because I have a need for a dashboard that a use can input data and update it.

#7

Thanks Costin! This is exactly what I was considering. Glad to hear that someone else has used it like this. I’ll give it a try!

1 Like