0

I have a view which contains a derived column, and I need to create a stored procedure which make an insert into the view

I've tried

CREATE PROCEDURE INSERTInVIEW
(
    @ID DECIMAL(10,2),
    @Name ....,
    @Address....,
    @Phone
)
AS
BEGIN
    INSERT INTO MyView (ID, Name, Address, Phone)
    VALUES (@ID, @Name, @Address, @Phone)
END

where the Address is a computed field in the view. I getting the error that the insert failed because it contains derived or computed field. Which should be the best way to do this insert?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Orsi
  • 545
  • 2
  • 10
  • 27
  • What type of SQL database is this? – devlin carnate Jun 24 '21 at 21:25
  • @devlincarnate - it's Sql Server Management Studio v18.4 – Orsi Jun 24 '21 at 21:27
  • Insert into original table columns. – Serg Jun 24 '21 at 21:29
  • yes, I know. If I insert into the original tables it works, but it was a request to insert also into the view and I didn't find any good working solution – Orsi Jun 24 '21 at 21:32
  • @devlincarnate you can insert into views that meet the criteria for updatable views. This inserts into the base table. Or you can use INSTEAD OF triggers to insert into other views – Martin Smith Jun 24 '21 at 21:40
  • 1
    You can insert into a view following certain rules, but you can't insert into a computed column... how would that work? – Dale K Jun 24 '21 at 21:40
  • 1
    Why are you trying to explicitly insert a value for address when that is a calculated column? – Martin Smith Jun 24 '21 at 21:41
  • Who made the request for this? Was it a supervisor with SQL knowledge who you can just explain this to or a client insisting they be able to just type in an address in one field instead of multiple? – pwilcox Jun 24 '21 at 21:50
  • 1
    You said it yourself, you have a *derived column* - that's a column that's *made up*, it's not part of the actual table (if it were, you would just select it); you can't insert data into a *made up* column - where would it go? how would you retrieve it? – Stu Jun 24 '21 at 22:37

1 Answers1

0

If your view is based on multiple tables it's likely not updateable. Try checking the information_schema tables:

SELECT IS_UPDATEABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLENAME = 'YourTableName';
Russell Fox
  • 5,273
  • 1
  • 24
  • 28