| Author |
 Topic  |
|
|
anilr499
Starting Member
18 Posts |
 Posted - 06/26/2012 : 03:54:10
|
hi
i have critical problem ..
i have a stored procedure for which we need to give 2 inputs...
stored procedure name: SP_GET_KPI_NEW_INFO
parameters: date,prj_id
by this stmt am executing store proc
exec SP_GET_KPI_NEW_INFO '03/02/2012', 'PRJ0000673'
but am joining the result of this SP to many tables which has
many prj_id's....
how can i exec this SP for all prj_id's in my DB
and store in a table...
because i need to do this in back end...
is that possible to execute my SP for many prj_id's??????..
am using asp,vbscript in my application
can some body help meee or suggest me how can i proceed....
thank you... |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/26/2012 : 04:27:40
|
where are your prj_id's?
declare @s table (prj_id varchar(100))
insert @s select prj_id from mytbl
declare @r table (whatever the result format is)
declare @prj_id varchar(100) = ''
while @prj_id < (select max(prj_id) frrom @t)
begin
select @prj_id = min(prj_id) frrom @t where prj_id > @prj_id
insert @r exec SP_GET_KPI_NEW_INFO '03/02/2012', @prj_id
end
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
anilr499
Starting Member
18 Posts |
Posted - 06/27/2012 : 00:30:57
|
hi nigelrivett,
thanks for your support...
i tried like this
CREATE TABLE spitable (
PRJ_ID VARCHAR(50),
C_PRJ_TITLE VARCHAR(50),
C_CBWS DECIMAL(10,2),
C_ACWP DECIMAL(10,2),
C_BCWP DECIMAL(10,2),
C_SV DECIMAL(10,2),
C_PCT_CPL DECIMAL(10,2),
C_SPI DECIMAL(10,2)
)
declare @prj_id varchar(100) = ''
while @prj_id < (select max(prj_id) from project)
begin
select @prj_id = min(prj_id) from project where prj_id > @prj_id
insert into spitable
exec SP_MTR_TIMELINES_SPI_NEW '@sprj_id'
end
project:is the table from which i will get prj_id(project id)
it is executing but am getting like this..
(0 row(s) affected)
many times ...and even not one record also getting effected...
finally am getting an empty table.....
can you fix the problem....
its urgent...
thank you... |
|
|
| |
Topic |
|
|
|
how can i execute stored procedure for all my prj_
