Parameter help

Last Post 24 Jan 2007 10:09 AM by hienzs. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
hienzs
New Member
New Member

--
24 Jan 2007 07:09 AM
Hi and thanks for reading.

I am calling a stored procedure from excel via vba ado. I can not seem to get the output value back into excel. The parameter created in ado is null. Not sure what I am doing wrong. Any help appreciated.

stored procedure abbreviated
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
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' +
hienzs
New Member
New Member

--
24 Jan 2007 10:09 AM
Thanks worked perfect
You are not authorized to post a reply.

Acceptable Use Policy