ctrllior ctrllior - 11 days ago 5
SQL Question

Temp view in stored procedure is valid only on first UPDATE

I'm writing a stored procedure that creates a temporary view and then perform multiple updates based on this temp view.
The problem is that this temporary view is not valid after the first UPDATE command (error is "Invalid object name").

USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_MySP]
@passedParam VARCHAR(32)
AS
BEGIN
WITH MyTempView AS
(
SELECT [myTable1].[Id]
FROM [dbo].[myTable1] LEFT JOIN [dbo].[myTable2] ON [myTable1].Id = [myTable2].[DeviceId]
WHERE
--all kind of conditions
)

--The temp view is valid here
UPDATE [dbo].[myTable1]
SET [myTable1].[Ready] = 0, [myTable1].[Reason] = NULL
WHERE
[myTable1].[Id] IN (SELECT [Id] FROM MyTempView)

--The temp view is NO LONGER VALID from this point
UPDATE [dbo].[myTable1]
SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 1.')
WHERE
--all kind of conditions

UPDATE [dbo].[myTable1]
SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 2.')
WHERE
--all kind of conditions

UPDATE [dbo].[myTable1]
SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 3.')
WHERE
--all kind of conditions

UPDATE [dbo].[myTable1]
SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 4.')
WHERE
--all kind of conditions

END


how can that be solved ?
Thanks in advance.

Answer

Yes Temp view will be invalid because scope of cte is limited to first update statement. So you insert results into temp table. And use it down in procedure where ever you want.

 USE [MyDB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[SP_MySP]
        @passedParam VARCHAR(32)
    AS
    BEGIN
       if object_id('tempdb..#t1') is not null
        drop table #t1
            SELECT [myTable1].[Id] into #t1
            FROM [dbo].[myTable1] LEFT JOIN [dbo].[myTable2] ON [myTable1].Id = [myTable2].[DeviceId]
            WHERE 


    --The temp view is valid here
    UPDATE [dbo].[myTable1]
    SET [myTable1].[Ready] = 0, [myTable1].[Reason] = NULL
    WHERE
    [myTable1].[Id] IN (SELECT [Id] FROM #t1)

    --The temp view is NO LONGER VALID from this point
    UPDATE [dbo].[myTable1]
    SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 1.')
    WHERE
    --all kind of conditions

    UPDATE [dbo].[myTable1]
    SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 2.')
    WHERE
    --all kind of conditions

    UPDATE [dbo].[myTable1]
    SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 3.')
    WHERE
    --all kind of conditions

    UPDATE [dbo].[myTable1]
    SET [myTable1].[Reason] = CONCAT([myTable1].[Reason],'Reason 4.')
    WHERE
    --all kind of conditions

    END
Comments