[dba-SQLServer] "SELECT @SQL" - A bit lost.

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
>
>


More information about the dba-SQLServer mailing list