Count # of Rows in Stored Procedure Result, then I

问题:

I have an SSIS package which will first run my sp_doSomething. This stored procedure will select data from several different tables and join them for possible storage into dbo.someTable. But I only want that IF the select is > 1 row of selected data.

I want to then have a precedence restraint that looks at the amount of rows my stored procedure returned.

If my row count > 1, then I want to take the results of the stored procedure and insert them into one of my tables.

Otherwise, I will record an error/send an email, or whatever.

I really don't want to run this stored procedure more then once, but that is the only way I could think to do it (Run it, count the rows. Then, run it again and insert the result).

I'm a complete TSQL/SSIS newb. So I'm sorry if this question is trivial. I can't find a good answer anywhere.

回答1:

Create a variable with Package Scope of type Int32 and name rowcount.

Data Flow

Control Flow

回答2:

Within the Stored Proc, write the results to a #Temp. Then Select Count(*) from the #Temp, into a variable.

Select @intRows = Count(*) from myTempResults

Then evaluate the value of @intRows.

If @intRows > 1 BEGIN

Insert Into dbo.SomeTable 
Select * from #Temp 

End

回答3:

you can try this

declare @tableVar table(col1 varchar(100))
declare @Counter int
insert into @tableVar(col1)  exec CompanyNames

set @Counter = (select count(*) from @tableVar)
insert into Anytable(col) Values (@counter)

回答4:

Will a #temp table work for you?

IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
    drop table #Holder
end


CREATE TABLE #Holder
(ID INT )




declare @MyRowCount int
declare @MyTotalCount int = 0

/* simulate your insert, you would read from your real table(s) here */
INSERT INTO #HOLDER (ID) 
select 1 union all select 2 union all select 3 union all select 4

Select @MyRowCount = @@ROWCOUNT, @MyTotalCount = @MyTotalCount + @MyRowCount
Select 'TheMagicValue1' = @MyRowCount, 'TheMagicTotal' = @MyTotalCount

INSERT INTO #HOLDER (ID) 
select 5 union all select 6 union all select 7 union all select 8

/* you will note that I am NOT doing a count(*) here... which is another strain on the procedure */    

Select @MyRowCount = @@ROWCOUNT, @MyTotalCount = @MyTotalCount + @MyRowCount
Select 'TheMagicValue1' = @MyRowCount, 'TheMagicTotal' = @MyTotalCount

/* Optional index if needed */
CREATE INDEX IDX_TempHolder_ID ON #Holder (ID)
/* CREATE CLUSTERED INDEX IDX_TempHolder_ID ON #Holder (ID) */    


if @MyTotalCount > 0
BEGIN
    Select 'Put your INSERT statement here'
END


/* this will return the data to the report */
Select ID from #HOLDER


IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
    drop table #Holder
end