Mark Breen
marklbreen at gmail.com
Wed Sep 10 03:07:59 CDT 2014
Hello Darryl
thanks for the reply,
My question was not about the cast
Phrasing it differently,
could you use either of the following two lines
SELECT @SQL = @SQL + ' AND MaterialClassName = ' + CAST(@MatClassName as
NVARCHAR(6)) + ''
SELECT @SQL = @SQL + ' AND MaterialClassName = ' + CAST(@MatClassName as
NVARCHAR(6))
I do not see why you are appending on a zero length string to the @SQL
thanks
Mark
On 10 September 2014 02:58, Darryl Collins <darryl at whittleconsulting.com.au>
wrote:
> 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
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>