[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