Link to home
Create AccountLog in
Avatar of healthmanagement
healthmanagement

asked on

Complex sp executed in View with temporary table

I'm trying to create a report in excel. Where i retrive the results from a store procedure. My stored procedure creates a temporary table and then fires 6 other store procedure to populate the temporary table.

Normally to create a excel report, i just use the import external data and use a view and database connection. However these are normally simple views e.g. simple select statements inner joins etc
here is a simplified version of the sp
Declare @thisBusDay integer
Declare @userUID uniqueidentifier 
Declare @teamUID uniqueidentifier 
Declare @now datetime
Declare @isNoDateTest bit
 
set @isNoDateTest = 0
set @userUID = Null
set @teamUID = Null
 
select @now = getDate()
select @thisBusDay = tblSysBusDays.intBusSerial from tblSysBusDays where tblSysBusDays.uidDate = CAST(ROUND(CAST(getDate() AS float), 0, 1) AS datetime)
 
--temporary table for found tasks
-- =============================================
-- Create temp table
-- H3 2006-04-18 Updating telephone numbers to 50 chars to match tblOHPClinics
-- 40 H3 2006-05-03 changing field widths to make sure ok
-- =============================================
CREATE TABLE #FoundTasks (
caseUID uniqueidentifier, 
itemUID uniqueidentifier not null,
dtmRecallDate datetime,
isChase bit,
isPriorityCase bit,
strClinicianWkTel2 varchar(255),
ClinicianGen varchar(255)
)
--Case recall tasks
exec spGetAllTasks1_Recall  	@isNoDateTest , 	@userUID , 	@teamUID , @thisBusDay, @now
 
--Case FME chases except GP
exec spGetAllTasks2_FMEChase  	@isNoDateTest , 	@userUID , 	@teamUID,@thisBusDay, @now 
 
 
--Case client not informed
exec spGetAllTasks3_CaseClientNotInformed  	@isNoDateTest , 	@userUID , 	@teamUID, @thisBusDay 
 
--Case FME outcomes
exec spGetAllTasks4_FMEOutComes  	@isNoDateTest , 	@userUID , 	@teamUID ,@thisBusDay
 
--expired contract, or no current recurring charge
exec spGetAllTasks5_billingcheck  	@isNoDateTest , 	@userUID , 	@teamUID ,@thisBusDay, @now
 
--FME where the appointment is yet to be booked
exec spGetAllTasks6_BookAppt  	@isNoDateTest , 	@userUID , 	@teamUID ,@thisBusDay, @now
 
--clients with no contracts expiring after 90 days, or no current contract
exec spGetAllTasks7_ContractExpiry  	@isNoDateTest , 	@userUID , 	@teamUID ,@thisBusDay, @now
 
update #FoundTasks
set CTA = '-Not Assigned-' where CTA is null
 
update #FoundTasks
set strTeamName = '-Not Assigned-' where strTeamName is null
 
select strClientName, strCaseNumber, caseEmployee, dtmRecallDate, task, CTA, strTeamName from #FoundTasks

Open in new window

Avatar of James Elliott
James Elliott
Flag of United Kingdom of Great Britain and Northern Ireland image

I normally do this sort of thing through an ADO connection class that I created. Please find it below, together with an example of usage.

Jell

'#######Connection Class - needs to be called 'cSP'###########

Private dbConn As ADODB.Connection
Private fParam As ADODB.Parameter
Private fCmd As ADODB.Command
Private fStream As ADODB.Stream
Private fReturn As ADODB.Parameter
Private Const DB1_CONNECTION_STRING As String = "Driver={SQL Server};" & _
                                            "Server=;" & _
                                            "Database=;" & _
                                            "Uid=;" & _
                                            "Pwd="
Private Const DB2_CONNECTION_STRING As String = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Initial Catalog=;Data Source="

Public Property Get ReturnVal() As Variant
   ReturnVal = fReturn.Value
End Property

Public Sub Set_DB_AS_DB1()
     dbConn.ConnectionString = DB1_CONNECTION_STRING
End Sub

Public Sub Set_DB_AS_DB2()
    dbConn.ConnectionString = DB2_CONNECTION_STRING
End Sub

Public Sub Set_CreateCmdText(ByVal stStored_Proc_Name As String)
    fCmd.CommandText = stStored_Proc_Name
    fCmd.CommandType = adCmdStoredProc
End Sub

Public Sub CreateParam_Return_Integer()
   Set fReturn = New ADODB.Parameter
   fReturn.Type = adInteger
   fReturn.Direction = adParamReturnValue
   fReturn.Name = "@return"
   fCmd.Parameters.Append fReturn
End Sub

Public Sub CreateParam_Return_Long()
   Set fReturn = New ADODB.Parameter
   fReturn.Type = adInteger
   fReturn.Direction = adParamReturnValue
   fReturn.Name = "@return"
   fCmd.Parameters.Append fReturn
End Sub

Public Sub CreateParam_Integer(ByVal stName As String, ByVal iValue As Variant)
   Set fParam = New ADODB.Parameter
   fParam.Type = adInteger
   fParam.Direction = adParamInput
   fParam.Name = stName
   fParam.Value = iValue
   fCmd.Parameters.Append fParam
   Set fParam = Nothing
End Sub

Public Sub CreateParam_Float(ByVal stName As String, ByVal dblValue As Variant)
   Set fParam = New ADODB.Parameter
   fParam.Type = adDouble
   fParam.Direction = adParamInput
   fParam.Name = stName
   fParam.Value = dblValue
   fCmd.Parameters.Append fParam
   Set fParam = Nothing
End Sub

Public Sub CreateParam_Date(ByVal stName As String, ByVal aDate As String)
   Set fParam = New ADODB.Parameter
   fParam.Type = adDate
   fParam.Direction = adParamInput
   fParam.Name = stName
   fParam.Value = aDate
   fCmd.Parameters.Append fParam
   Set fParam = Nothing
End Sub

Public Sub CreateParam_VarChar(ByVal stName As String, iSize As Integer, stValue As Variant)
  Set fParam = New ADODB.Parameter
   fParam.Type = adVarChar
   fParam.Direction = adParamInput
   fParam.Size = iSize
   fParam.Name = stName
   fParam.Value = stValue
   fCmd.Parameters.Append fParam
   Set fParam = Nothing
End Sub

Public Sub CreateParam_Via_Stream(ByVal stName As String, stValue As String)              
   Set fStream = New ADODB.Stream
   fStream.Type = adTypeText
   fStream.Open
   fStream.Charset = "ASCII"
   fStream.WriteText stValue
   fStream.Position = 0
   Set fParam = New ADODB.Parameter
   fParam.Type = adLongVarChar
   fParam.Direction = adParamInput
   fParam.Size = fStream.Size
   fParam.Name = stName
   fParam.Value = fStream.ReadText
   fCmd.Parameters.Append fParam
   Set fParam = Nothing
   Set fStream = Nothing
End Sub

Public Function ExecuteStoredProc() As ADODB.Recordset
   dbConn.Open
   fCmd.ActiveConnection = dbConn
   Set ExecuteStoredProc = fCmd.Execute
End Function

Private Sub Class_Initialize()
   Set fCmd = New ADODB.Command
   Set dbConn = New ADODB.Connection
End Sub

Private Sub Class_Terminate()
   dbConn.Close
   Set dbConn = Nothing
   Set fCmd = Nothing
End Sub



'####Example of usage####

Option Explicit

Dim oSP As cSP
Dim rst As adodb.Recordset

Set oSP = New cSP
Set rst = New adodb.Recordset

oSP.Set_DB_AS_DB1
oSP.Set_CreateCmdText ("dbo.example_proc")
oSP.CreateParam_Integer "@your_parameter", 1
oSP.CreateParam_VarChar "@your_parameter2", 20, "Example VarChar"

Set rst = oSP.ExecuteStoredProc

'do something with your recordset

Set rst = Nothing
Set oSP = Nothing
Avatar of Anthony Perkins
I suspect I know the error message you are getting and the solution is very simply, but would you mind speliing out the error and/or the problem.
ASKER CERTIFIED SOLUTION
Avatar of healthmanagement
healthmanagement

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account