Calling a View in a Stored Procedure

Question

Dear All,

I am trying to call a View within a Stored Procedure but I don't know if it is at all possible please?

Thank you in advance

Answer 1

Here is my scenario:

We have an application which we use C#, but the way the Architecture for creating the Application is that only Views can be used to get the Data from the SQL. The trouble is, there are Data Manipulations (Delete, Updates etc...) that occur, but View would not be able to perform that. I just need to find a solution where the View could be updatable please?

 

Answer 2

I think you need to create stored procedures for DELETE/UPDATE/INSERT and not using views

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

"Calling a view" is a bit of a misnomer. A view is query, but you access it as it was a table.

It is possible to run UPDATE/DELETE/INSERT on a view, but SQL Server must be able to map the statement to a single table. Alternatively, the DBA can define INSTEAD OF triggers on the view that targets the operation to one or more tables.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

I'm not sure you can do that, but it will depend on the view.

If the view only refers to one table, you can do an update without problems. If it refers to several tables (JOINs) then you might not be able to to the update if you try to update columns that are in different tables. This is the same behaviour as if you do a simple update statement that tried to update values in two tables.

You can see the behaviour in the below example -

 

CREATE TABLE T1 (RowID INT, A VARCHAR(5))
CREATE TABLE T2 (RowID INT, B VARCHAR(5))
 

 INSERT INTO T1(RowID, A) VALUES (1,'Text1')
 INSERT INTO T2(RowID, B) VALUES (1,'Text2')

GO
CREATE VIEW V1 
AS
SELECT T1.RowID, T1.A, T2.B 
FROM T1 T1 
JOIN T2 T2 ON T1.RowID = T2.RowID

--This will work 
UPDATE V1
SET A = 'Text3'

-- This will cause an error
UPDATE V1
SET A='Text3'
, B= 'Text4'

DROP  TABLE T1

DROP TABLE T2

 

---

Steen Schlüter Persson (DK)