I have a stored procedure as follows :-

Create Procedure GetNewYear_mnth



as
Declare @OldYear int,
@OldMonth int,
@NewYear int,
@NewMonth int



select @Oldyear=max(uc_year_num) from tprocess_control
select @OldMonth=max(UC_MNTH_NUM ) from tprocess_control where
uc_year_num=@OldYear

If @OldMonth=12
begin
set @NewMonth=1 set @NewYear=@OldYear+1
end
else if

@oldMonth<12
begin
set @NewMonth=@OldMonth+1
set @NewYear=@Oldyear
end
drop table ##month
drop table ##year

Create table ##month(MonthNumber int)
insert into ##month
values(@Newmonth)

create table ##Year(YearNumber int)
insert into ##year
values(@Newyear)

I want to get the newyear and the newmonth values in a global variable
so that i can build a vbscript string in dts.

mySqlQuery = "Select count(*) CNT , 'TSLSDALY' TableName ,
SUM(EXT_HIGH_AMT) AMT,'InProcess' as Status From CDDBA.TSLSDALY
WHERE UC_mnth < '" & @Newmonth & "'UC_YR < '" & @NewYear & "'UC AND
Rpt_div_id in (1,2,3,4,5,6,7,14,16,35) "
..


How can i do this?


Thanks


Ajay garg

I would re-write the proc to return the two values as a single resultset at
the end, and remove the temp table stuff. e.g. SELECT @Newmonth as Newmonth,
@Newyear as Newyear
Make sure you add SET NOCOUNT ON to the top of the procedure.

Then use the output parameter function for an Exec SQL Task to get the two
values into global variables.

These can then be referenced in VBScript.

Or

If they are in SQL, on the same SQL Server, why not derive them as part of
the SELECT you build in VBScript, just make the same call in the SQL
statement.

--
Darren Green
http://www.sqldts.com

"Ajay Garg" <ajayz90 (AT) hotmail (DOT) com> wrote