Run a stored procedure and export its values for an entire month

Question

As it stands my colleague will be running the below query 30 times.

EXEC dbo.usp.Nameofstoredprocedure '01-nov-2016'

It exports 3 rows with columns ID, Name, type

Is there anyway to have it export as:

Date,       ID, name,    type
01-nov-2016,10,john smith,man
01-nov-2016,11,jane smith,woman
02-nov-2016,10,john smith, man
02-nov-2016,11,jane smith,woman

etc.. The stored procedure in question is not something I can copy and paste in due to policy.

Thinking it over, I can see a loop might work, possibly inserting the row into a table but I can't figure out how to do this, any assistance would be great.

Work so far:

declare @date date
set @date = '2016-11-01'
declare @inte int
while @inte >= 30
select * into #temp EXEC dbo.usp_GetMaxUsers @date
                                   set @date = DATEADD(dd,1,@date)
                                   set @inte = @inte + 1

Right now it's giving me the following error:

Msg 263, Level 16, State 1, Line 4
Must specify table to select from.
Msg 1038, Level 15, State 5, Line 4
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Thanks.

Try this. http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure — Sean Lange, Dec 01 '16 at 14:38

Wes Palmer · Answer 1 · 2016-12-01T15:09:19.870

2

You could try this:

CREATE TABLE <Table Name>
(
   Date DateTime,
   ID INT,
   Name NVARCHAR(40),
   Type NVARCHAR(6)
)

INSERT INTO <Table Name>
Exec <SP NAME> 'Params'

You need to run the create table only once of course then put the rest into an agent job to run whenever you need it to.

edited Dec 01 '16 at 15:09

answered Dec 01 '16 at 14:43

Wes Palmer

880
4
15

1

But the stored procedure does not return the `RunDate`. This would not work. – SS_DBA Dec 01 '16 at 14:44
I was referring to whatever Date you had in your example. Sorry it wasn;t clear. I edited the answer to reflect that. – Wes Palmer Dec 01 '16 at 15:08

score 1 · Answer 2 · answered Dec 01 '16 at 15:10

The real problem is the stored procedure should be redone to meet the requirements. Database objects often need refactoring too.

I would add an enddate optional parameter (with a default value of null, so it won't break existing uses of the proc). Then if only the first date was sent in, it would set the end date to the correct value to get only records from that date. Otherwise it would use both dates in the internal query(ies) to get the information over a date range.

score 0 · Accepted Answer · answered Dec 01 '16 at 15:21

0

The answer that worked.

declare @date2 date
set @date2 = '2016-11-01'
declare @inte int=0

while @inte <= 29
begin

create table #temp
(
 ID int,
 Name varchar(100),
 Type varchar(50)
)

insert into #temp
exec [dbo].[usp_proceduregoeshere] @date2

insert into #Results
select @date2, *
from #Agents

drop table #temp

set @date2 = dateadd(day, 1, @date2)
set @inte = @inte+1
end

select *
from #Results

answered Dec 01 '16 at 15:21

Anon Ymouse

101
5

1

What happens when you run this for February? You should get the last day of the month based on your first date then loop based on the end date being reached. – SS_DBA Dec 01 '16 at 15:57

Run a stored procedure and export its values for an entire month

3 Answers3