how to monitor updating data real-time by sored procedure string query in MVC?

Question

What methods can I use to monitor updating data real-time ? Returned data from following stored procedure that write as string query :

BEGIN TRAN

USE [MyDataBase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[MySp]
(    
    @UserId INT
)
as
BEGIN

CREATE TABLE #tmp (T1 INT,T2 NVARCHAR(500))
declare @sql nvarchar(max)
SET @sql='select  [T1], [dbo].[MyFunction]('+ cast(@UserId as nvarchar(20))+',[T1],0) as [T2]
          from [dbo].[MyTable]               
          where ( [X] in(50,60) or [Y] in(520,530) ) and  [dbo].[MyFunction]('+ cast(@UserId as nvarchar(20)) +',[T1],0) <>0'

    sp_executesql @sql        
END

EDIT 1 :

The procedure non string query is :

BEGIN TRAN

USE [MyDataBase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[MySp]
(    
    @UserId INT
)
as
BEGIN

select  [T1], [dbo].[MyFunction]('+ cast(@UserId as nvarchar(20))+',[T1],0) as [T2]
          from [dbo].[MyTable]               
          where ( [X] in(50,60) or [Y] in(520,530) ) and  [dbo].[MyFunction]('+ cast(@UserId as nvarchar(20)) +',[T1],0) <> 0       
END

But also this method not correct working.What to do?

score 0 · Answer 1 · edited May 23 '17 at 12:14

0

SQLDependency doesn't work with temporal tables.

Please see What are the limitations of SqlDependency

edited May 23 '17 at 12:14

Community

1
1

answered Jun 07 '15 at 08:43

Jesús López

8,338
7
40
66

I do not have a temporary table, however, does not work. – Mohsen Jun 11 '15 at 09:27
And what is this: "CREATE TABLE #tmp (T1 INT,T2 NVARCHAR(500))" – Jesús López Jun 11 '15 at 11:13
Please see the Edit1. – Mohsen Jun 13 '15 at 07:00
And what about MyFunction, Is it deterministic?. Does it access data in the database? – Jesús López Jun 13 '15 at 07:11
Yes,Is it deterministic.Why not working?what to do for return result؟What do I use for this item?how change procedure to return true result? – Mohsen Jun 13 '15 at 07:27

how to monitor updating data real-time by sored procedure string query in MVC?

1 Answers1