October 24, 2022 at 4:07 am
I encode Insert and update statements in a user-defined funtion on some tables(not temp table and not table variable) in SQL Server, but when I use Execute command to create this function in SQL Server management studio, it shows Invalid use of a side-effecting operator 'UPDATE' within a function. Invalid use of a side-effecting operator 'INSERT' within a function.
Is there any approach to let insert/update/delete statement work in user-defined function? thanks!
October 24, 2022 at 7:02 am
What are you trying to accomplish?
Why should a UDF be able to handle that?
Check "Valid statements in a function"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 25, 2022 at 12:58 am
Dear Johan Bijnens, I want to use a user-defined function to update some data in physical table if possible, when I searched and got a post, it was said that it seems that we can use sqlcmd -S to meet my requirement. so I want to consult it with experts if we can use sqlcmd or how we can use sqlcmd to achieve it, thanks
October 25, 2022 at 1:15 pm
Please read the links Johan provided.
If you want to modify data, then you need to use a stored procedure, not a user-defined function.
In SQL Server, a user-defined function is used only to return data-- a single value (scalar functions) or a table (table-valued-functions). Stored procedures are much more flexible. Then can modify and/or select data. Then can return data in output parameters.
I'm not sure how sqlcmd relates to this. It is simply a client (command-line) to interact with SQL Server, as are SQL Server Management Studio & Azure Data Studio (GUI). sqlcmd can be used in SSMS Query Editor when set to SQLCMD mode.
October 25, 2022 at 2:07 pm
I suspect this may be an OP spam post ....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 17, 2022 at 7:55 am
Lookup the MERGE command.
Functions perform operations in a row by row manner as opposed to a set at a time. This is slow and not needed.
I am not sure how you are thinking about automating this as CRUD operations are DML one time operations. Dynamic SQL could be risky here if you were leaning towards that.
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply