Darryl Collins
darryl at whittleconsulting.com.au
Tue Sep 9 20:58:43 CDT 2014
Ok.... I seem to have this one working ok now - largely thanks to some guidance from you folks - so a big thank you for your advice and feedback.
As suspected - it was partly an issue of ignorance and tiredness. I actually had two problems to correct.
The first one corrected id'd by David Lewis being the IF statements in the SPROC were not correctly formed. I did try this yesterday, but clearly I fouled it up as today it is working fine.
The 2nd issue was the VBA code I use to pass the parameter to SQL server was not set up correctly - I was passing thru a couple of the parameters in the wrong order. That was easy enough to spot once I got the SPROC to manually fire correctly.
Mark was asking about the use of the syntax using CAST
SELECT @SQL = @SQL + ' AND MaterialClassName = ' + CAST(@MatClassName as NVARCHAR(6)) + ''
The reason I need to do this is the Server is expected to execute a string and unlike VBA built strings you need to explicitly convert anything that is not a string (so all the integer parameters passed into the sproc in this example) into strings *before* it is executed. You can use either CAST or CONVERT for this. I have always used CAST which has performed flawlessly, but that comes down to ignorance and luck. There may be a good / better reason to use CONVERT instead but as CAST has always worked for me I have been happy to stick with it for now.
The VBA that passes the parameters is below - this works great now I have the darn append parameter lines in the correct sequence.
'============================================================================================================
Public Function BuildInOut_Summary_SPOnly_SSSP(Procedure As String, _
SourceTable As String, _
ProdClassType As Integer, _
ProdClassName As Integer, _
ProdName As Integer, _
Activity As Integer, _
MatClassName As Integer _
) As Boolean
Dim cmd As New ADODB.Command
On Error GoTo ErrHandle
With cmd
.ActiveConnection = gDbADOConStr
.CommandType = adCmdStoredProc
.CommandText = "spBuildInOut_Summary_SPOnly"
End With
With cmd.Parameters
.Append cmd.CreateParameter("Procedure", adVarChar, adParamInput, 100, Procedure)
.Append cmd.CreateParameter("SourceTable", adVarChar, adParamInput, 100, SourceTable)
.Append cmd.CreateParameter("ProdClassType", adInteger, adParamInput, , ProdClassType)
.Append cmd.CreateParameter("ProdClassName", adInteger, adParamInput, , ProdClassName)
.Append cmd.CreateParameter("ProdName", adInteger, adParamInput, , ProdName)
.Append cmd.CreateParameter("Activity", adInteger, adParamInput, , Activity)
.Append cmd.CreateParameter("MatClassName", adInteger, adParamInput, , MatClassName)
End With
cmd.Execute
Set cmd = Nothing
Exit Function
ErrHandle:
BuildInOut_Summary_SPOnly_SSSP = False
End Function
'============================================================================================================
So the functional SQL SERVER SPROC code looks like this now:
ALTER PROCEDURE [dbo].[spBuildInOut_Summary_SPOnly]
@Procedure NVARCHAR(100),
@SourceTable NVARCHAR(100),
@ProdClassType int,
@ProdClassName int,
@ProdName int,
@Activity int,
@MatClassName int
AS
BEGIN
-- SELECT NOCOUNT ON added to prevent extra result SELECTs from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(Max)
SELECT @SQL = 'INSERT INTO'
SELECT @SQL = @SQL + ' Temp_tblEveSteps_Temp'
SELECT @SQL = @SQL + '('
SELECT @SQL = @SQL + 'ProcedureClassType,'
SELECT @SQL = @SQL + ' ProcedureClassName,'
SELECT @SQL = @SQL + ' ProcedureName,'
SELECT @SQL = @SQL + ' MaterialClassName,'
SELECT @SQL = @SQL + ' MaterialTypeName,'
SELECT @SQL = @SQL + ' QuantityTypeName,'
SELECT @SQL = @SQL + ' SeqID_ProcClassManual,'
SELECT @SQL = @SQL + ' SeqID_QuanType,'
SELECT @SQL = @SQL + ' SeqID_MatClassType,'
SELECT @SQL = @SQL + ' PortionType,'
SELECT @SQL = @SQL + ' Activity,'
SELECT @SQL = @SQL + ' ActivityFocus,'
SELECT @SQL = @SQL + ' FromActivity,'
SELECT @SQL = @SQL + ' FromFocus'
SELECT @SQL = @SQL + ')'
SELECT @SQL = @SQL + ' SELECT'
SELECT @SQL = @SQL + ' ProcedureClassType,'
SELECT @SQL = @SQL + ' ProcedureClassName,'
SELECT @SQL = @SQL + ' ProcedureName,'
SELECT @SQL = @SQL + ' MaterialClassName,'
SELECT @SQL = @SQL + ' MaterialTypeName,'
SELECT @SQL = @SQL + ' QuantityTypeName,'
SELECT @SQL = @SQL + ' SeqID_ProcClassManual,'
SELECT @SQL = @SQL + ' SeqID_QuanType,'
SELECT @SQL = @SQL + ' SeqID_MatClassType,'
SELECT @SQL = @SQL + ' PortionType,'
SELECT @SQL = @SQL + ' Activity,'
SELECT @SQL = @SQL + ' ActivityFocus,'
SELECT @SQL = @SQL + ' FromActivity,'
SELECT @SQL = @SQL + ' FromFocus'
SELECT @SQL = @SQL + ' FROM ' + @SourceTable + ''
SELECT @SQL = @SQL + ' GROUP BY'
SELECT @SQL = @SQL + ' ProcedureClassType,'
SELECT @SQL = @SQL + ' ProcedureClassName,'
SELECT @SQL = @SQL + ' ProcedureName,'
SELECT @SQL = @SQL + ' MaterialClassName,'
SELECT @SQL = @SQL + ' MaterialTypeName,'
SELECT @SQL = @SQL + ' QuantityTypeName,'
SELECT @SQL = @SQL + ' SeqID_ProcClassManual,'
SELECT @SQL = @SQL + ' SeqID_QuanType,'
SELECT @SQL = @SQL + ' SeqID_MatClassType,'
SELECT @SQL = @SQL + ' PortionType,'
SELECT @SQL = @SQL + ' Activity,'
SELECT @SQL = @SQL + ' ActivityFocus,'
SELECT @SQL = @SQL + ' FromActivity,'
SELECT @SQL = @SQL + ' FromFocus'
SELECT @SQL = @SQL + ' HAVING ProcedureClassType = ' + CAST(@ProdClassType as NVARCHAR(6)) + ''
SELECT @SQL = @SQL + ' AND ProcedureClassName = ' + CAST(@ProdClassName as NVARCHAR(6)) + ''
-------------------------------------------------------------------------------------------------------
If @Procedure = 'Mining'
BEGIN
SELECT @SQL = @SQL + ' AND ProcedureName = ' + CAST(@ProdName as nvarchar(6)) + ''
END
If @Procedure = 'Downstream'
BEGIN
SELECT @SQL = @SQL + ' AND ProcedureName = ' + CAST(@ProdName as nvarchar(6)) + ''
END
-------------------------------------------------------------------------------------------------------
If @Procedure = 'Stockpile'
BEGIN
SELECT @SQL = @SQL + ' AND ActivityFocus = ' + CAST(@Activity as nvarchar(6)) + ''
END
ELSE IF @Procedure = 'Discard'
BEGIN
SELECT @SQL = @SQL + ' AND ActivityFocus = ' + CAST(@Activity as nvarchar(6)) + ''
END
ELSE IF @Procedure = 'Rehandle'
BEGIN
SELECT @SQL = @SQL + ' AND FromActivity = ' + CAST(@Activity as nvarchar(6)) + ''
END
ELSE
SELECT @SQL = @SQL + ' AND Activity = ' + CAST(@Activity as nvarchar(6)) + ''
-------------------------------------------------------------------------------------------------------
SELECT @SQL = @SQL + ' AND MaterialClassName = ' + CAST(@MatClassName as NVARCHAR(6)) + ''
--PRINT @sql
EXECUTE (@SQL)
END
'============================================================================================================
Thanks everyone.
Cheers
Darryl