how can i execute stored procedure for all my prj_

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how can i execute stored procedure for all my prj_

Author

Topic

anilr499
Starting Member

18 Posts

Posted - 2012-06-26 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-26 : 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 - 2012-06-27 : 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...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources