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

mssql sqlfunction

anniecat 2019-03-22 11:08:54 ‧ 6770 瀏覽

分享至

結論 : 一定要使用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')

出現以下的錯誤

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

想詢問兩個問題 :

想確認此方法確實沒有其他方式可以達到嗎?
若有，要怎麼回傳id呢?

leonliu777 iT邦新手 5 級 ‧ 2019-03-22 11:15:13 檢舉

只回傳id的話用純量值函式
select InsertToDB(null,'twst123')

anniecat iT邦新手 3 級 ‧ 2019-03-22 11:25:30 檢舉

不好意思，不太明白你說的，但有找了一下純量值函式的範例，
不知道理解對不對，在我的範例中存在著value可能重複的可能，
所以不太知道要如何在存入後及時將產生的id傳給變數做回傳呢?

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 8 級 ‧ 2019-03-22 11:31:53

[id] [int] identity(1,1) not null,
select InsertToDB(null,'test123') <== null ??
set @cmd = 'insert into '+ '[dbo].[testTable]' + 'values (' + @id + ',' + @value + ')'
=> INSERT INTO "表格名" ("欄位1", "欄位2", ...) VALUES ("值1", "值2", ...);

回應 2
分享
檢舉

anniecat iT邦新手 3 級 ‧ 2019-03-22 13:14:34 檢舉

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

anniecat iT邦新手 3 級 ‧ 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]'。

登入發表回應

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