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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy