[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