撰寫Stored Procedure小細節

 參考引用:[SQL SERVER]撰寫Stored Procedure小細節

---

撰寫Stored Procedure小細節

EXEC AssetLibrary_AssetDeleteAttributes @AssetID

declare @myquery nvarchar(4000);

set @myquery = 'SELECT * from dbo.test2 where c1 = @id';

--第一次

execute sp_executesql @myquery,N'@id int', @id = 1;

--第二次

execute sp_executesql @myquery,N'@id int', @id = 2;

--查看執行計畫

SELECT cap.usecounts as '使用次數',objtype as '快取類型',st.text

FROM sys.dm_exec_cached_plans cap

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE st.text not like '%sys%'

and st.text like '%dbo.test2%'

DECLARE @myquery VARCHAR(8000)

,@myid int;

set @myid = 5;

set @myquery = 'select * from dbo.test2 where c1 = '+ CAST( @myid as varchar);

--第一次

exec (@myquery) 

set @myid = 6; 

set @myquery = 'select * from dbo.test2 where c1 = '+ CAST(@myid as varchar );

--第二次

exec (@myquery) 

SELECT cap.usecounts as '使用次數',objtype as '快取類型',st.text            

FROM sys.dm_exec_cached_plans cap

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE st.text not like '%sys%'

and st.text like '%dbo.test2%'

vb.net 背景執行

 背景執行+回報進度+顯示進度條+中斷程序

使用 BackgroundWorker 再次上傳

VB.net Thread 執行序

 引用來源:VB.net Thread執行序簡單應用

建立一個Class用來執行新的Thread:

Imports System.Threading

Namespace Classes

Public Class Worker

Delegate Sub WorkerDelegate(ByVal Params As Object)

Private m_Thread As Thread = Nothing

Private m_Controler As Control = Nothing

Private m_Params As Object = Nothing

Public Event WorkerToDo(ByRef Params As Object)

Public Event WorkerInvoke(ByVal Params As Object)

Dim CallBackInvoke As New WorkerDelegate(AddressOf Worker_Invoke)

Public Sub New(ByVal ctrl As Control)

m_Controler = ctrl

End Sub

Public Sub Start()

Dim ThreadBegin As New ThreadStart(AddressOf Worker_ToDo)

Me.m_Thread = New Thread(ThreadBegin)

Me.m_Thread.IsBackground = True

Me.m_Thread.Name = "WorkerThread" + Now.ToShortTimeString()

Me.m_Thread.Start()

End Sub

Private Sub Worker_ToDo()

RaiseEvent WorkerToDo(m_Params)

m_Controler.Invoke(CallBackInvoke, m_Params)

End Sub

Private Sub Worker_Invoke(ByVal Params As Object)

RaiseEvent WorkerInvoke(Params)

End Sub

End Class

End Namespace

使用方式:

Private Sub LoadData()

Dim wk As New Worker(Me)

Try

'連結要丟到背景工作的副程式

AddHandler wk.WorkerToDo, AddressOf DoQueryDataBase

'工作完畢後, 所要處理資料(與UI互動)

AddHandler wk.WorkerInvoke, AddressOf BindData

wk.Start()

Finally

wk = Nothing

End Try

End Sub

Private Sub DoQueryDataBase(ByRef Params As Object)

'執行的程式, 結果可由 Params 代傳

'1: Params = modADO.Query(...)

'2: Params = New Object() {data1, data2, ...}

End Sub

Private Sub BindData(ByVal Params As Object)

'背景工作完畢後, 與 UI 互動

TextBox1.Text = CInt(Params)

End Sub