update and return another string in a SQL function

Question

I want to update a string and return a string in a SQL function. The update works perfectly but the returning the string is not working,when i deactivate the sp_executesql the function returns the value. why we can't execute a command and return a different value. please help me the SQL FUNCTION

===========================================================
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    set nocount on
    go
===========================================================
    ALTER FUNCTION [dbo].[autoid](@tablename varchar(max))
    RETURNS varchar(max)
    AS
     -- Add the parameters for the stored procedure here
    BEGIN          


    declare @str1 varchar(max)
    declare @str2 varchar(max)
    declare @str3 varchar(max)
    declare @str4 varchar(max)
    declare @str5 varchar(max)
    declare @str6 varchar(max)
    declare @str7 nvarchar(max)
    declare @str8 int
    declare @str9 int
    declare @str10 int
    declare @str11 int
    declare @str12 int
    select @str8 = IDNO from control where tablename =  @tablename 
    select @str1 = shortname from control where tablename = @tablename 
    set @str3=  @str1
    set @str9=LEN(@str8)
    set @str11=26
    select @str10= @str11 - @str9
    select @str12=@str8 + 1
    while(@str10>0)
    BEGIN
    set @str1=@str1 + '0'
    select @str10 = @str10 - 1
    END
    select @str5 = @str1 + CAST ( @str12 AS VARCHAR(10) ) 
    set @str7='update control set IDNO = ' + CAST ( @str12 AS VARCHAR(100)) + ' where tablename = ''' + @tablename +''''
    execute sp_executesql @str7
    return @str5
    END
    ====================================================================================

Answer 1

You would need to use a stored procedure for this. Functions can't have side effects including Updating tables.

You also can't execute stored procedures (including sp_executesql) from within a function (or at least not without resorting to a hack with openrowset)

You can return scalar values from a stored procedure by using output parameters.