Mark Breen
marklbreen at gmail.com
Tue Sep 9 03:20:20 CDT 2014
Hi Darryl,
Just curious why you have '' at the end of some lines, eg SELECT @SQL =
@SQL + ' AND MaterialClassName = ' + CAST(@MatClassName as NVARCHAR(6)) + ''
I am guessing you are using them as some sort of closing exercise, but
curious what the reason is ?
Re your problem, I would do my usual trick of back to the beginnning and
evaluate each step, getting them to known good before moving on.
Are the inserts working, are they clean?
Is the first If firing?
Sometimes in a longish sproc, I introduce a variable
Declare @FireNotifications bit = 1
And then in various sections of the sproc, I include one discreet line
similar to the following
If @FireNotifications = 1 Select @Procedure
At other times, I do this
If @FireNotifications =1 Insert into LogTable (Values.....) Select MyValues
to review when proc is finished......
Sorry I could not see the problem
HTH
On 9 September 2014 07:52, David Emerson <newsgrps at dalyn.co.nz> wrote:
> 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
>
>