How to edit records in SQL-Server stored procedure

Question

I would like to know the secret of how SQL statements in SQL-Server go from being read-only to editable. Right click on any table, and the interface gives the option of "Selecting" or "Editing" records. Is there a property in the SQL statement that designates the recordset as editable or read-only?

I will use the simplest possible example: I have designed a table with two fields: an integer field, designated as an identity and a unique index. The second is an nvarchar, designed for manual editing. Writing a query window, I write a SQL statement for the table, and I am not able to edit the text field. Also, Stored procedures, which I favor because I can evoke them with the greatest effeciency, also renders an uneditable recordset. The only way I have found to succeed is in SSMS, when choosing the edit feature on a table.

I use Microsoft Access extensively, and all the tables that Access hosts are linked to SQL-Server tables. When I use the Microsoft Access JET engine to write queries on these same tables, I can edit the recordsets the queries generate, but not when I use pass-through queries to evoke the same contents in a table function or stored procedure. With no table joins, no calculated fields, nor anything else that would impose a known reason for me not to be able to edit the recordsets, the inability poses a barrier to producing some of my deliverables.

Thanks, in advance, for your support. Here are quick examples:

Select
    IDField
,   TextField
From
    SampTable

Create Procedure TestProc
AS
BEGIN
    Select
        IDField
    ,   TextField
    From
        SampTable
END

Create FUNCTION [dbo].[TestFunction]()
RETURNS TABLE 
AS
RETURN 
(
    Select
        IDField
    ,   TextField
    From
        SampTable
)

Answer 1

SQL Server is not the same kind of thing as MS Access. MS access is a combination of front-end and back end at the same time, which is nice and easy for users, and does have its place. It's like a souped up version of excel with some very limited multi user functionality. But with SQL Server, the expectation is that you are splitting the responsibilities between front end and back end.

Yes, SSMS does provide the ability to right click a table (or a view referencing one table) and "edit top 200 rows". Honestly, I wish it didn't. It shouldn't.

If you have an access "front end" using linked tables in SQL Server in the "back end", that's similar functionality. And yeah, there are some limited uses cases where that's an appropriate sort of solution, ideally as a temporary thing. But really, if you're putting data into SQL Server, the expectation is that you're building some kind of "real" user interface, which uses DML statements constructed by the application, or stored procedure execution, or some kind of ORM and DBContext, to modify the data. Even in MS Access, you should switch from direct table editing to forms.

The reason why you can't edit the results of a stored procedure or function is that the output of those objects is just a temporary copy of the data. It's not the "actual data in the tables". And, if you think about it, how could it be? For example, imagine if I wrote a stored procedure like this:

create table t (i int primary key, j int);

create procedure p as begin
   select total_j = sum(j) from t;
end

When I run that stored procedure I'm going to get a single value which is the sum of j across all rows. How could I edit this value? If I changed it from, say, 100 to 200, what does that mean in terms of the contents of column j in the table? Do I add 100 to some arbitrary row? Do I add 1 to each of the first 100 rows in order of the primary key? The concept becomes incoherent.

I know what you're thinking: "But what if my stored procedure doesn't aggregate? Surely then the data that comes back can really just be a "pointer" to the data in the table, not a copy?". And yeah, in principle that could be true. But think about the implications of that. While you're looking at the results, can anyone else change the underlying data in the table? Can you both change it at the same time? Who decides how to resolve that problem - the SQL engine? Can someone else drop the table while you are editing data? And so on and so forth.

It's the wrong way to think about SQL Server (or any "real" database engine). The data you see as the result of a select is read from the tables, and sent over the network to the client as your own personal copy. It is no longer connected to the tables it came from.

Oh... and in case you're wondering how you can edit the data "directly in the tables" if you're using linked tables in MS Access: you still can't. Access does some work under the covers for you. To prove this, try linking a SQL Server table to MS Access, then pulling up the row in access, and starting to edit it. Then, before finishing your edit, go in to SSMS and update the row you are editing in access. Then try to save your changes in Access.