Post reply

update stmt in Function?

  • December 8, 2007 at 2:50 am

    #127226

    Hello everyone, i m using SQL Server 2000, i made this function but when i run it , it gives me following error:

    "Invalid use of Update within a function?" pplz check it and tell me how i made function with Update stmt?

    Here is my Function kindly check it and plz reply me.

    CREATE FUNCTION SetConfigKey(@SystemConfigAccessKey varchar(64) , @ConfigKey varchar(128),@ConfigValuevarchar(8000))

    RETURNS varchar(8000)

    AS

    BEGIN

    declare @SystemConfigID int

    select @SystemConfigID = SystemConfigID

    from SystemConfigs

    where AccessKey = @SystemConfigAccessKey

    Update SystemConfigKeys

    SetValue= @ConfigValue

    whereSystemConfigID = @SystemConfigID

    And ConfigKey= @ConfigKey

    return@ConfigValue

    END

    Thanx in Advance.

  • December 8, 2007 at 3:53 am

    #758278

    Hi,

    None of the DML statements can be used in a User Defined Function(UDF)

    Hence the update being one of them fails.

    Regards

    Avaneesh Bajoria.

  • December 9, 2007 at 11:40 pm

    #758473

    Functions are not allowed to have side effects. No data changes, no schema changes, no config changes.

    Can you convert it to a stored procedure?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 10, 2007 at 12:13 am

    #758485

    why don't you just return the ids of the values that match the criteria in a table and then do an update on the rows where the ids are in the rows returned in the function's table?


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply