Update Within a SQL Server Function

Question

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

score 5 · Answer 1 · edited Aug 08 '19 at 17:47

5

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

edited Aug 08 '19 at 17:47

Tsahi Asher

1,767
15
28

answered Aug 26 '13 at 14:44

Rahul Tripathi

168,305
31
280
331

2

But stored procedures cannot be called from other SPs as Functions can. I thought I had read that updating through the variable would work. It doesn't error, it just doesn't update. – Bob Phillips Aug 27 '13 at 15:06
Workaround does not work. UPDATE #tbl1 SET X = getx(tbl1.Z), Y = getx(tbl1.W) UPDATE #tbl2 SET X = getx(tbl2.Y) ... doesn't convert into a stored procedure very well. – Joshua Oct 23 '15 at 16:45

score 0 · Answer 2 · edited Aug 08 '19 at 13:41

0

You can't modify the table data inside a function.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017

edited Aug 08 '19 at 13:41

Tsahi Asher

1,767
15
28

answered Aug 26 '13 at 14:38

Sonam

3,406
1
12
24

score 0 · Answer 3 · answered Aug 26 '13 at 14:44

It looks like you're attempting to update the table variable, not the physical table itself. Also, as others have mentioned, you can't update a physical table's data in a function. You CAN do this using a stored procedure, however. Do you have a requirement that dictates that a FUNCTION needs to be used?

Update Within a SQL Server Function

3 Answers3