Using a Stored Procedure for an Audit Log

[Chadrick]

I am trying to create an Audit Log that will keep track of the data changes made by users. To do this, I created a stored procedure in SQL but Radzen is not inferring it. I reviewed this post, but I’m not sure what the workaround is Stored Procedures missing from interfered schema

Assuming that can be resolved, what is the syntax to call a stored procedure and pass things along like OldValue & NewValue of a field?

Thanks so much!
Chadrick

[joshbooker]

If the first batch in your sproc doesn’t return a result set then it will not be in the dm view that radzen uses to infer the schema. The solution is to make sure the first statement in your sproc is a select statement. The workaround is to add a dummy select statement to your sproc like

select * from dbo.Dummy

As explained here

Using complex stored procedures with Radzen

Just passing on a workaround for those complex stored procedures that Radzen (specifically the Microsoft built in function ‘sys.dm exec describe first result set for object’) does not ‘Infer’ or allow you to add to your project. If the stored procedure is primarily for server side transaction processing and not to return dataset then a simple dummy select call will allow Radzen to ‘Infer’ the procedure and be added to your project for an ‘Invoke’. For example: ALTER PROCEDURE [dbo].[usp_GetDe…

You also need to be on sql server 2012 and if you want to use the results in radzen then make your dummy results resemble the actual sproc resultset.

HTH josh