0

My goal is to update a table with a string "finished" and some static values (in the other columns in the table), based on a select statement and conditional that the output from the select statements fulfil certain conditions. These conditions are that the output from around 18 stored procedures are returned with a value=0.

Each stored procedure returns some value ("Number_of") for each unit ("EnhetsId"), ranging from 0 to infinity. The stored procedures first calls a database ('GR_PS09_1') and then a unique unit based on its unit ID ('EnhetsId').

So first I have a select statement, that given a certain condition gives me an output of a number of units. Then, if these units fulfil the condition that the SP returns the value 0 for each of the units, I want to insert into a temp-table the "qualified" unit ID (EnhetsId), some static values and the string "finished".

My code so far, with help from @MrReband:

declare @temp2 table (
EnhetsId varchar(50), 
TjanstId Int, 
Tabell varchar(50),
Kommentar ntext,
Uppdaterad datetime 
);


WITH ENHET_CTE AS 
(
SELECT A.[EnhetsId]
FROM [StatistikinlamningDataSKL].[dbo].[StatusHistorik] A
inner join (
         select [EnhetsId], max(SenastUppdaterad) as SenastDatum
         from [StatistikinlamningDataSKL].[dbo].[StatusHistorik]
         group by [EnhetsId]
         ) B
on A.[EnhetsId] = B.[EnhetsId] and A.[SenastUppdaterad] = B.SenastDatum
WHERE [NyStatus] = 4
),        
 /* this code below is the one I am trying to solve */
SP_01 AS
(
set @enhet = (select exec StatistikinlamningDataSKL.dbo.SKL_admin_KN_aform 'GR_PS09_1', '''EnhetsId'''
where 'Number_of' = 0)

), 

/*add more stored procedures*/

insert into @temp2 
(EnhetsId, TjanstId, Tabell, Kommentar, Uppdaterad) 
SELECT 
EnhetsId, 1, 'GR_PS09_1', 'finished', getdate() 
from ENHET_CTE;

Does anyone have some input on how to proceed with this code?

Community
  • 1
  • 1
user2995808
  • 51
  • 1
  • 9
  • This looks suspiciously like SQL Server, despite the MySQL tag? If you have a stored procedure that returns a single value have you considered using a scalar user defined function? – GarethD Nov 19 '13 at 13:48
  • Thanks @GarethD, Its SQL Server for sure. The stored procedures returns a single value for each unit, so since I have many units returned from the first select-statement, it will be many single values. I am new to "create function"-coding. Using a scalar function, will the function be "called/executed" for each unit? Moreover, I guess you mean that I should create a function for each stored procedure that I have? – user2995808 Nov 19 '13 at 14:04
  • You could declare another table variable and use an approach similar to this answer to store the results of the stored procedure: http://stackoverflow.com/a/3963991/1768890 – mr.Reband Nov 19 '13 at 17:55
  • thanks for the idea, I will look into it as soon as I have the time :) Thanks for all help! – user2995808 Nov 22 '13 at 17:59

0 Answers0