Darryl Collins
darryl at whittleconsulting.com.au
Tue Sep 9 18:52:53 CDT 2014
Thanks David,
Appreciate your thoughts, time and efforts with this.
I did try some BEGIN / END combinations on the IF's but didn't seem to get it right. Mind you it was getting late in the day and I was frustrated so I will start again fresh today. Part of the problem will be my own darn ignorance. I have used SQL Server for years, but my actual knowledge is 'wider' rather than deeper - if that makes sense. I am pretty much a one trick pony and have done well with that trick but I often struggle when doing some of the more tricky stuff in SQL Server - Google can often help but in this instance I have found nothing for the "ah-hah!" moment.
These little blocks are all good as I will learn some new stuff and better ways to do things so I really appreciate everyone's thoughts and patience here.
I had already tried the (most excellent) suggestion of cutting it down and staring with something simple.
I tried just the SELECT part of the SQL String with a table name hard coded - and I still got nothing - which was a worry.
Anyway - today is a new day and I am feeling fresh. So time to get cracking after I install my 25 Update Wednesday MS updates and reboot - (yeah, I know it is update Tuesday, but we get them on Wednesday due to those pesky time zones).
Will let you know how all this works out.
Cheers
Darryl
From: David Lewis [mailto:David at sierranevada.com]
Sent: Wednesday, 10 September 2014 1:35 AM
To: Darryl Collins
Subject: sql string
Darryl:
Your issue is at the end with the several IF statements. After an IF statement explicitly wrap the code to execute within BEGIN END blocks, as with the following. Also, you have an ELSE statement that is important, but it is not clear where that ELSE should be evaluated. Here is non-sproc code to test with. Also, when you are building strings like this, printing out the string without executing is the way to debug.
Up to the final ELSE statement, I think the code with BEGIN END blocks does what you are expecting.
DECLARE @Procedure NVARCHAR(100), @SourceTable NVARCHAR(100), @ProdClassType int, @ProdClassName int, @ProdName int, @Activity int, @MatClassName int
DECLARE @SQL NVARCHAR(Max)
SET @SourceTable='TestTable'
SET @Procedure='Discard'
SET @ProdClassType=0
SET @ProdClassName=1
SET @ProdName=2
SET @Activity=3
SET @MatClassName=4
SELECT @SQL = 'INSERT INTO'
PRINT @sql
SELECT @SQL = @SQL + ' Temp_tblEveSteps_Temp'
PRINT @sql
SELECT @SQL = @SQL + '('
PRINT @sql
SELECT @SQL = @SQL + 'ProcedureClassType,'
PRINT @sql
SELECT @SQL = @SQL + ' ProcedureClassName,'
PRINT @sql
SELECT @SQL = @SQL + ' ProcedureName,'
PRINT @sql
SELECT @SQL = @SQL + ' MaterialClassName,'
PRINT @sql
SELECT @SQL = @SQL + ' MaterialTypeName,'
PRINT @sql
SELECT @SQL = @SQL + ' QuantityTypeName,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_ProcClassManual,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_QuanType,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_MatClassType,'
PRINT @sql
SELECT @SQL = @SQL + ' PortionType,'
PRINT @sql
SELECT @SQL = @SQL + ' Activity,'
PRINT @sql
SELECT @SQL = @SQL + ' ActivityFocus,'
PRINT @sql
SELECT @SQL = @SQL + ' FromActivity,'
PRINT @sql
SELECT @SQL = @SQL + ' FromFocus'
PRINT @sql
SELECT @SQL = @SQL + ')'
PRINT @sql
SELECT @SQL = @SQL + ' SELECT'
PRINT @sql
SELECT @SQL = @SQL + ' ProcedureClassType,'
PRINT @sql
SELECT @SQL = @SQL + ' ProcedureClassName,'
PRINT @sql
SELECT @SQL = @SQL + ' ProcedureName,'
PRINT @sql
SELECT @SQL = @SQL + ' MaterialClassName,'
PRINT @sql
SELECT @SQL = @SQL + ' MaterialTypeName,'
PRINT @sql
SELECT @SQL = @SQL + ' QuantityTypeName,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_ProcClassManual,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_QuanType,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_MatClassType,'
PRINT @sql
SELECT @SQL = @SQL + ' PortionType,'
PRINT @sql
SELECT @SQL = @SQL + ' Activity,'
PRINT @sql
SELECT @SQL = @SQL + ' ActivityFocus,'
PRINT @sql
SELECT @SQL = @SQL + ' FromActivity,'
PRINT @sql
SELECT @SQL = @SQL + ' FromFocus'
PRINT @sql
SELECT @SQL = @SQL + ' FROM ' + @SourceTable + ''
PRINT @sql
SELECT @SQL = @SQL + ' GROUP BY'
PRINT @sql
SELECT @SQL = @SQL + ' ProcedureClassType,'
PRINT @sql
SELECT @SQL = @SQL + ' ProcedureClassName,'
PRINT @sql
SELECT @SQL = @SQL + ' ProcedureName,'
PRINT @sql
SELECT @SQL = @SQL + ' MaterialClassName,'
PRINT @sql
SELECT @SQL = @SQL + ' MaterialTypeName,'
PRINT @sql
SELECT @SQL = @SQL + ' QuantityTypeName,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_ProcClassManual,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_QuanType,'
PRINT @sql
SELECT @SQL = @SQL + ' SeqID_MatClassType,'
PRINT @sql
SELECT @SQL = @SQL + ' PortionType,'
PRINT @sql
SELECT @SQL = @SQL + ' Activity,'
PRINT @sql
SELECT @SQL = @SQL + ' ActivityFocus,'
PRINT @sql
SELECT @SQL = @SQL + ' FromActivity,'
PRINT @sql
SELECT @SQL = @SQL + ' FromFocus'
PRINT @sql
SELECT @SQL = @SQL + ' HAVING ProcedureClassType = ' + CAST(@ProdClassType as NVARCHAR(6)) + ''
PRINT @sql
SELECT @SQL = @SQL + ' AND ProcedureClassName = ' + CAST(@ProdClassName as NVARCHAR(6)) + ''
PRINT @sql
-------------------------------------------------------------------------------------------------------
If @Procedure = 'Mining'
BEGIN
SELECT @SQL = @SQL + ' AND ProcedureName = ' + CAST(@ProdName as nvarchar(6)) + ''
PRINT 'First IF ' + @sql
END
If @Procedure = 'Downstream'
BEGIN
SELECT @SQL = @SQL + ' AND ProcedureName = ' + CAST(@ProdName as nvarchar(6)) + ''
-------------------------------------------------------------------------------------------------------
PRINT 'Second IF ' + @sql
END
If @Procedure = 'Stockpile'
BEGIN
SELECT @SQL = @SQL + ' AND ActivityFocus = ' + CAST(@Activity as nvarchar(6)) + ''
PRINT 'Third IF ' + @sql
END
IF @Procedure = 'Discard'
BEGIN
SELECT @SQL = @SQL + ' AND ActivityFocus = ' + CAST(@Activity as nvarchar(6)) + ''
PRINT ' Fourth IF ' + @sql
END
IF @Procedure = 'Rehandle'
BEGIN
SELECT @SQL = @SQL + ' AND FromActivity = ' + CAST(@Activity as nvarchar(6)) + ''
PRINT 'Fifth IF ' + @sql
END
ELSE
SELECT @SQL = @SQL + ' AND Activity = ' + CAST(@Activity as nvarchar(6)) + ''
-------------------------------------------------------------------------------------------------------
SELECT @SQL = @SQL + ' AND MaterialClassName = ' + CAST(@MatClassName as NVARCHAR(6)) + ''
--EXECUTE (@SQL)
PRINT @sql
________________________________
The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission. If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.