iT邦幫忙

0

[已解決]詢問在function中執行insert的方法

結論 : 一定要使用SP才能作業

如下為原本的問題內容

//-----------------------------------------------------------

自訂函數無法對Table進行INSERT、UPDATE與DELETE的操作,
預存程序才能進行。

但我仍然嘗試了以下這個方法 :

  • 首先創建了一個Table做測試
create table [dbo].[testTable](
	[id] [int] identity(1,1) not null,
	[value] [nvarchar](max) null
) 
  • 並自訂函數希望可以將傳進來的值存入Table並回傳其id
create function InsertToDB(
	@value nvarchar(MAX)
)
returns nvarchar(1000)
as
begin
	declare @cmd nvarchar(max)  
    
	set @cmd = 'insert testTable output inserted.id values ('+ @value +')'
        
	exec sp_executesql @cmd 
    
    --這邊想要回傳insert進去的id,但不知如何作業
	return 'sucess' 
end
  • 呼叫function
select [dbo].InsertToDB('test123')
  • 出現以下的錯誤

只有函數和一些擴充預存程序可以從函數內執行。

想詢問兩個問題 :

  1. 想確認此方法確實沒有其他方式可以達到嗎?
  2. 若有,要怎麼回傳id呢?
只回傳id的話 用純量值函式
select InsertToDB(null,'twst123')
anniecat iT邦新手 5 級 ‧ 2019-03-22 11:25:30 檢舉
不好意思,不太明白你說的,但有找了一下純量值函式的範例,
不知道理解對不對,在我的範例中存在著value可能重複的可能,
所以不太知道要如何在存入後及時將產生的id傳給變數做回傳呢?

2 個回答

1
rogeryao
iT邦研究生 3 級 ‧ 2019-03-22 11:31:53
  1. [id] [int] identity(1,1) not null,
    select InsertToDB(null,'test123') <== null ??
  2. set @cmd = 'insert into '+ '[dbo].[testTable]' + 'values (' + @id + ',' + @value + ')'
    => INSERT INTO "表格名" ("欄位1", "欄位2", ...) VALUES ("值1", "值2", ...);
anniecat iT邦新手 5 級 ‧ 2019-03-22 13:14:34 檢舉

謝謝您把我的問題指出來,已更新我的發問內容。

anniecat iT邦新手 5 級 ‧ 2019-03-22 15:47:57 檢舉

不好意思,好像有一點明白了,您是想表達若此用參數的話,就可以達成嗎?
但我現在改寫成以下的方式,還是會有錯誤訊息,請問是否有那裡寫錯了呢?

create function InsertToDB(
	@value nvarchar(MAX)
)
returns nvarchar(1000)
as
begin    
    insert into "[dbo].[testTable]"("value") values (@value)
    
    --這邊想要回傳insert進去的id,但不知如何作業
	return 'sucess' 
end

--exec
select [dbo].InsertToDB('test123')

無效的物件名稱 '[dbo].[testTable]'。

2
sam0407
iT邦高手 1 級 ‧ 2019-03-23 09:16:01

自動編號的取得您可以參考這篇文章:
MSSQL取得剛新增資料的自動編號

另外在MS SQL裡的函數是不能對資料庫中的資料進行修改操作,就是不能新增、修改、刪除資料庫中的資料。所以,你想在函數中向table inster資料是作不到的,但您可以用stored procedure來作,以下是簡單的範例:

ALTER PROCEDURE [dbo].[sp_InsertToDB] 
	@value nvarchar(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @id int
    insert into testTable([value]) values (@value);
	select scope_identity()
END
anniecat iT邦新手 5 級 ‧ 2019-03-25 10:12:32 檢舉

原本是想使用參數的方式看能否達成,但實驗後無法實現,看起來是一定要使用SP才能作業,感謝您的回覆。

我要發表回答

立即登入回答