CREATE PROCEDURE sp_StrokeProc 
@@TBLName varchar(25) OUTPUT 
AS 

declare @cellerr int, @edate datetime, @test datetime, @sdate datetime, @pysdate datetime, @pyedate datetime, 
@fsdate datetime, @fedate datetime, @pyfsdate datetime, @pyfedate datetime, @Den as dec(9,5), 
@Num as dec(9,5), @result as dec(9,5), @sql varchar(8000), @MaxDate datetime, 
@MaxDate1 datetime, @MinDate datetime, @err int 

set @cellerr = 0 
set @result = 0 

delete from [current] 

CREATE TABLE #DTSErrorOutput 
(ErrorString varchar(500) NULL) 
INSERT #DTSErrorOutput 

EXEC [master].[dbo].[xp_cmdshell] 'DTSRun /S "(local)" /N "MemIn" /G "{86F4EA7F-3D55-42F3-82B5-8762D4112431}" /W "0" /E' 

Set @err = (SELECT DISTINCT Count(LTRIM(ErrorString)) AS ErrCount 
FROM #DTSErrorOutput WHERE ErrorString LIKE '%Error string%') 

DROP TABLE #DTSErrorOutput 

if @err <> 0 
Begin 
Return 1 
End 

--copy records to permanent table 
Set @MaxDate = (SELECT MAX(gs_discdatetime) AS Max_Date FROM Historic) 
Set @MaxDate1 = (SELECT MAX(gs_discdatetime) AS Max_Date FROM [Current]) 

if @MaxDate > @MaxDate1 
Begin 
INSERT INTO Historic SELECT [Current].* FROM [Current] 
End 
else 
begin 
Set @MinDate = (SELECT Min(gs_discdatetime) AS Min_Date FROM [Current]) 
Delete from Historic Where gs_discdatetime >= @MinDate and gs_discdatetime <= @MaxDate1 
INSERT INTO Historic SELECT [Current].* FROM [Current] 
end 

--start and end dates for actual 
--sdate 
set @sdate = (select min(gs_discdatetime) FROM [Current]) 
set @sdate = (select DATEADD(mm, DATEDIFF(mm,0,@sdate), 0)) 
--edate 
set @edate = (select max(gs_discdatetime) FROM [Current]) 
set @edate = (select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@edate)+1, 0))) 

--start and end dates for prior year actual 
--pysdate 
set @pysdate = (select dateadd(year, -1 , @sdate)) 
--pyedate 
set @pyedate = (select dateadd(year, -1 , @edate)) 

--start and end dates for fiscal year actual 
If Month(@edate) > 9 
Begin 
--fiscal start and end dates 
set @fsdate = convert(datetime, cast(year(@sdate)as varchar) + '-10-' + cast(day(@sdate)as varchar),101) 
set @fedate = @edate 
End 
else 
Begin 
set @fsdate = convert(datetime, cast(year(@sdate)- 1 as varchar) + '-10-' + cast(day(@sdate)as varchar),101) 
set @fedate = convert(datetime, cast(year(@edate) as varchar) + '-9-' + cast(day(@edate)as varchar),101) 
End 

--prior year fiscal period 
set @pyfsdate = dateadd(year, -1 , @fsdate) 
set @pyfedate = dateadd(year, -1 , @fedate) 

set @cellerr = 0 
set @result = 0 

if datename(qq, @edate) = 1 
begin 
set @@TBLName = 'StrokeScorecard' + Cast(Year(@edate) - 1 as varchar) + 'Q4' 
end 
else 
begin 
set @@TBLName = 'StrokeScorecard' + Cast(Year(@edate) as varchar) + 'Q' + Cast(Datename(q, @edate) as varchar) 
end 

set @sql = 'drop table ' + @@TBLName 
execute(@sql) 

--create aggregate scorecard table 
set @sql = 'CREATE TABLE ' + @@TBLName + ' ([ID] [int] IDENTITY (1, 1) NOT NULL ,' + 
' [Metric] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,' + 
'[Month_Actual] [decimal](18, 5) NULL CONSTRAINT [DF_' + @@TBLName + 'Month_Actual] DEFAULT (0),' + 
' [Month_Target] [decimal](18, 5) NULL , [Month_Prior_Year] [decimal](18, 5) NULL CONSTRAINT' + 
' [DF_' + @@TBLName + '_Month_Prior_Year] DEFAULT (0), [FYTD_Actual] [decimal](18, 5) NULL' + 
' CONSTRAINT [DF_' + @@TBLName + '_FYTD_Actual] DEFAULT (0), [FYTD_Target] [decimal](18, 5) NULL ,' + 
' [FYTD_Prior_Year] [decimal](18, 5) NULL CONSTRAINT [DF_' + @@TBLName + '_FYTD_Prior_Year] DEFAULT (0),' + 
' [User_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT' + 
' [DF_' + @@TBLName + '_User_ID] DEFAULT (user_id()), [Source] [varchar] (250)' + 
' COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Vendor_Metric_Name] [varchar] (250)' + 
' COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Cellerr_Month_Actual] [BIT] NULL ,' + 
' [Cellerr_Month_Prior_Year] [BIT] NULL , [Cellerr_FYTD_Actual] [BIT] NULL , [Cellerr_FYTD_Prior_year]' + 
' [BIT] NULL , [ExcelSheet] [int] NULL , CONSTRAINT [PK_' + @@TBLName + '] PRIMARY KEY CLUSTERED' + 
' ([ID]) ON [PRIMARY]) ON [PRIMARY]' 
Execute(@SQL) 

set @sql = ' INSERT INTO ' + @@TBLName + ' SELECT [Metric], [Month_Actual], [Month_Target], ' + 
' [Month_Prior_Year], [FYTD_Actual], [FYTD_Target], [FYTD_Prior_Year], [User_ID], [Source],' + 
' [Vendor_Metric_Name], [Cellerr_Month_Actual], [Cellerr_Month_Prior_Year], ' + 
' [Cellerr_FYTD_Actual], [Cellerr_FYTD_Prior_year], [ExcelSheet] FROM SourceStrokeScoreCard' 
Execute(@sql) 

--Documentation of onset percent Month_Actual num / den * 100 ID 3 
SET @num = (SELECT COUNT(patient_os_id) FROM [Historic] WHERE (Not (gs_onsetdetermined = '4')) 
AND (gs_discdatetime >= @sdate) AND (gs_discdatetime <= @edate)) 

SET @den = (SELECT Count(patient_os_id) FROM [Historic] 
WHERE (gs_discdatetime >= @sdate) AND (gs_discdatetime <= @edate)) 

if @Num > .00000 AND @Den >= @Num 
Begin 
set @Result = (@Num / @Den * 100) 
End 

if @Num > @Den or @Den = .00000 
Begin 
set @cellerr = 1 
set @Result = 0 
End 

set @sql = 'UPDATE ' + @@TBLName + ' SET Month_Actual =' + Convert(varchar(10), @Result) + 
', cellerr_Month_Actual = ' + Cast(@cellerr as varchar) + ' WHERE (ID = 3)' 
execute(@sql) 
select @@TBLName as TBLName 
return 
GO 
 

sServer = "myserver" 
sDatabase = "mydb" 
sUser = "myaccount" 
sPWD = "mypassword" 
' 
DBcon.ConnectionString = "Provider=sqloledb;" & _ 
"server=" & sServer & ";uid=" & sUser & ";pwd=" & sPWD & ";database=" & sDatabase 
DBcon.CursorLocation = adUseClient 
DBcon.Open 

objCmd.ActiveConnection = DBcon 
objCmd.CommandType = adCmdStoredProc 
objCmd.CommandText = "sp_StrokeProc" 

Set objparameter = objCmd.CreateParameter(TBLName, adVarChar, adParamOutput, 25) 
objCmd.Parameters.Append objparameter 
objCmd.Parameters.Refresh 
objCmd.Execute