Update table with result from stored procedure/query?

[Kirsten Østerga]

I have a table that I insert data into using the following stored procedure:

CREATE PROCEDURE [dbo].[usp_insertTotaler] 
AS
BEGIN
    SET NOCOUNT ON;
 
        DECLARE @Periode DateTime, @NyPeriode DateTime
    SET @NyPeriode = (SELECT Max(Periode) FROM InstPeriode)
 
    IF EXISTS 
        (SELECT DISTINCT ip.Periode FROM InstPeriode ip LEFT OUTER JOIN Totaler t ON ip.Periode = t.Periode 
        WHERE t.Periode Is Null)
    BEGIN
    INSERT INTO Totaler(iNavn, Periode, SubBrutSum, BrutTotal)
 
    EXEC dbo.usp_displayThemAll
    @Periode = @NyPeriode
    SELECT iNavn, Periode, SubBrutSum, BrutTotal
    FROM Totaler
    END

My question is: Is it possible to make an updatequery that executes a stored procedure (usp_displayThemAll) and then update my table (Totaler) with these data? And if it is - then how?

[ck9663]

Yes you can...

If you execute the display before the update, you're going to display the un-updated records.

Also, displaying the records will not do anything if you don't push it to your front-end. SQL Server is a Database, not a front-end tool...

Good Luck!!!

~~CK

[Kirsten Østerga]

It doesn't seem to me like I'm using SQL as a front-end tool? What's wrong with creating a stored procedure, and then push it to my front end? (Which is what I do - don't worry).

Perhaps I misunderstand you, but your answer seems a bit unkind...

[ck9663]

The comment was based on the assumption that your stored procedure is called DisplayAll. Which to me says the intention is to display the record.

If you want to update a table based on the resultset returned by that stored procedure, there are a number of options.

1. Read the stored proc, retrieve the query and use it to update your table.

2. Convert the stored proc into a table-valued function and then call it to insert to your table.

3. If you really want to use that stored procedure, you still can. Here, read this...

Happy Coding!!!

~~ CK