locked
Calling a View in a Stored Procedure RRS feed

  • 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

    Wednesday, November 16, 2011 11:35 AM

Answers

  • 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)
    Wednesday, November 16, 2011 2:08 PM
  • "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, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, November 16, 2011 2:08 PM
    • Marked as answer by Kalman Toth Tuesday, November 22, 2011 4:54 AM
    Wednesday, November 16, 2011 2:02 PM

All replies

  • 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?

     

    Wednesday, November 16, 2011 12:10 PM
  • 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/
    Wednesday, November 16, 2011 12:12 PM
    Answerer
  • "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, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, November 16, 2011 2:08 PM
    • Marked as answer by Kalman Toth Tuesday, November 22, 2011 4:54 AM
    Wednesday, November 16, 2011 2:02 PM
  • 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)
    Wednesday, November 16, 2011 2:08 PM