Update Within a SQL Server Function
30,617
No you cannnot do this. Functions are used for readonly
purpose.
User-defined functions cannot be used to perform actions that modify the database state.
Check this out
Work around is to create a STORED PROCEDURE
Author by
Bob Phillips
Updated on August 09, 2020Comments
-
Bob Phillips almost 4 years
I have a SQL Server function that gets a nextID from a table. That is straight-forward enough, but I also want to update the table for the ID used. I am using the technique to update into a table, but the update just isn't happening. The code is shown below, anyone know why?
CREATE FUNCTION [dbo].[GetNextKeyID] ( @user nvarchar(10), @keytype nvarchar(20) ) RETURNS nvarchar(15) AS BEGIN DECLARE @NextID nvarchar(15); -- get the next id for this keytype SELECT @NextID = @user + '_' + @keytype + '_' + CONVERT(VARCHAR(3), (SELECT CASE WHEN @keytype = 'sess' THEN SessionNext WHEN @keytype = 'event' THEN EventNext WHEN @keytype = 'evtc' THEN EventConsultantNext WHEN @keytype = 'act' THEN ActivityNext END) ) FROM dbo.[BARKeys] WHERE [UserID] = @user -- increment the next id for this keytype DECLARE @BARKeys TABLE ( UserID nvarchar(10), SessionNext int, EventNext int, EventConsultantNext int, ActivityNext int ) UPDATE @BARKeys SET SessionNext = CASE WHEN @keytype ='sess' OR @keytype = 'All' THEN SessionNext + 1 ELSE SessionNext END ,EventNext = CASE WHEN @keytype ='event' OR @keytype = 'All' THEN EventNext + 1 ELSE EventNext END ,EventConsultantNext = CASE WHEN @keytype ='evtc' OR @keytype = 'All' THEN EventConsultantNext + 1 ELSE EventConsultantNext END ,ActivityNext = CASE WHEN @keytype ='act' OR @keytype = 'All' THEN ActivityNext + 1 ELSE ActivityNext END WHERE [UserID] = @user -- return the next id RETURN @NextID ; END