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

Yep. And I get nothing.  Not sure why as it looks ok. Clearly I am missing something here. Hmmm.


Sent from my Galaxy Note II. Please excuse any gibberish and/or brevity.


-------- Original message --------
From: David Emerson
Date:09/09/2014 16:55 (GMT+10:00)
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] "SELECT @SQL" - A bit lost.

Hi Darryl,

Have you tried putting PRINT(@SQL) at the end of the procedure to see that
the string is creating correctly?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Darryl
Collins
Sent: Tuesday, 9 September 2014 6:28 p.m.
To: Discussion concerning MS SQL Server
Subject: [dba-SQLServer] "SELECT @SQL" - A bit lost.

Hi everyone,

I have done this sort of thing in the past and it has worked fine, but I
cannot seem to get this to work.

I am taking this approach as I am passing thru the table name as a variable
plus using IF statements to alter the code depending of certain criteria

I could just rewrite these into numerous stored procs with fixed table names
and criteria without all of this bother, but this way seemed neater and more
elegant - I have used this approach a lot in VBA without issue.

Anyone got any suggestions as to why the 'EXEC (@SQL)' returns nothing?  -
oh, I have checked that it should return 'something' - and indeed stripped
out all of the criteria so it has to return something, but I get nothing.
The SQL string doesn't seem to build in the Sproc at all.   It is hopefully
something stupid and obvious I have done here.

Using 2012 Express.

Cheers
Darryl.

'==============================================================
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'
                SELECT @SQL = @SQL + ' AND ProcedureName = ' +
CAST(@ProdName as nvarchar(6)) + ''

                If @Procedure = 'Downstream'
                SELECT @SQL = @SQL + ' AND ProcedureName = ' +
CAST(@ProdName as nvarchar(6)) + ''

----------------------------------------------------------------------------
---------------------------

        If @Procedure = 'Stockpile'
                        SELECT @SQL = @SQL + ' AND ActivityFocus = ' +
CAST(@Activity as nvarchar(6)) + ''

                ELSE IF @Procedure = 'Discard'
                        SELECT @SQL = @SQL + ' AND ActivityFocus = ' +
CAST(@Activity as nvarchar(6)) + ''

                ELSE IF @Procedure = 'Rehandle'
                        SELECT @SQL = @SQL + ' AND FromActivity = ' +
CAST(@Activity as nvarchar(6)) + ''

                ELSE
                        SELECT @SQL = @SQL + ' AND Activity = ' +
CAST(@Activity as nvarchar(6)) + ''

----------------------------------------------------------------------------
---------------------------

        SELECT @SQL = @SQL + ' AND MaterialClassName = ' +
CAST(@MatClassName as NVARCHAR(6)) + ''


        EXECUTE (@SQL)

END

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com