Salakhetdinov Shamil
mcp2004 at mail.ru
Thu Oct 23 17:12:01 CDT 2014
Hi Darryl --
This my posting not an answer on your question - just an advise (for future quiet analysis time) to considerably reconsider your dynamic SQL and temp tables usage in MS SQL stored procedures: I, personally, would avoid using them by any means.
Here is for starters your refactored SP and a sample UDF - they have to be refactored more as I'm not aware what are your source tables and how do you further use your temp tables.
Avoiding dynamic SQL on MS SQL server side makes your T-SQL much cleaner and safer (from SQL Injection etc.) as well as making it manageable by DBAs( if any) etc.
CREATE PROCEDURE [dbo].[spBuildInOut_Summary_SPOnly_NoDynamicSQL]
@Procedure NVARCHAR(100),
@SourceTable NVARCHAR(100),
@ProdClassType int ,
@ProdClassName int,
@ProdName int,
@Activity int,
@MatClassName int
AS
BEGIN
INSERT INTO
-- do you really need temp tables
-- or you can use table valued UDFs?
Temp_tblEveSteps_Temp
(
ProcedureClassType,
ProcedureClassName,
ProcedureName,
MaterialClassName,
MaterialTypeName,
QuantityTypeName,
SeqID_ProcClassManual,
SeqID_QuanType,
SeqID_MatClassType,
PortionType,
Activity,
ActivityFocus,
FromActivity,
FromFocus
)
SELECT
ProcedureClassType,
ProcedureClassName,
ProcedureName,
MaterialClassName,
MaterialTypeName,
QuantityTypeName,
SeqID_ProcClassManual,
SeqID_QuanType,
SeqID_MatClassType,
PortionType,
Activity,
ActivityFocus,
FromActivity,
FromFocus
-- can you use a UDF here ?
FROM MySourceTableFor_spBuildInOut_Summary(@SourceTable)
GROUP BY
ProcedureClassType,
ProcedureClassName,
ProcedureName,
MaterialClassName,
MaterialTypeName,
QuantityTypeName,
SeqID_ProcClassManual,
SeqID_QuanType,
SeqID_MatClassType,
PortionType,
Activity,
ActivityFocus,
FromActivity,
FromFocus
HAVING
ProcedureClassType = @ProdClassType AND
ProcedureClassName = @ProdClassName AND
(
(@Procedure = 'Mining' and ProcedureName = @ProdName) OR
(@Procedure = 'Downstream' and ProcedureName = @ProdName) OR
(@Procedure = 'Stockpile' and ActivityFocus = @Activity) OR
(@Procedure = 'Discard' and ActivityFocus = @Activity) OR
(@Procedure = 'Rehandle' and FromActivity = @Activity) OR
(Activity = @Activity)
) AND
MaterialClassName = @MatClassName
END
-----------------------------
CREATE FUNCTION [dbo].[MySourceTableFor_spBuildInOut_Summary]
(
@SourceTable NVARCHAR(100)
)
RETURNS @MySourceTable TABLE
(
ProcedureClassType int,
ProcedureClassName int,
ProcedureName int,
MaterialClassName int,
MaterialTypeName int ,
QuantityTypeName int ,
SeqID_ProcClassManual int,
SeqID_QuanType int,
SeqID_MatClassType int,
PortionType int,
Activity int ,
ActivityFocus int NULL,
FromActivity int ,
FromFocus int NULL
)
AS
begin
if @SourceTable = 'SourceOne'
insert into @MySourceTable SELECT * FROM [SourceOne] -- just a sample placeholder
else if @SourceTable = 'SourceTwo'
insert into @MySourceTable SELECT * FROM [SourceTwo] -- just a sample placeholder
else
insert into @MySourceTable SELECT * FROM [SourceX] -- just a sample placeholder
RETURN
end Thank you.
-- Shamil
P.S. BCC-ied to Darryl to bypass 20KB posting size moderation limitation.
Wed, 10 Sep 2014 01:58:43 +0000 from Darryl Collins <darryl at whittleconsulting.com.au>:
>Ok.... I seem to have this one working ok now - largely thanks to some guidance from you folks - so a big thank you for your advice and feedback.
>
>As suspected - it was partly an issue of ignorance and tiredness. I actually had two problems to correct.
>
>The first one corrected id'd by David Lewis being the IF statements in the SPROC were not correctly formed. I did try this yesterday, but clearly I fouled it up as today it is working fine.
>
>The 2nd issue was the VBA code I use to pass the parameter to SQL server was not set up correctly - I was passing thru a couple of the parameters in the wrong order. That was easy enough to spot once I got the SPROC to manually fire correctly.
>
>Mark was asking about the use of the syntax using CAST
>
>SELECT @SQL = @SQL + ' AND MaterialClassName = ' + CAST(@MatClassName as NVARCHAR(6)) + ''
>
>The reason I need to do this is the Server is expected to execute a string and unlike VBA built strings you need to explicitly convert anything that is not a string (so all the integer parameters passed into the sproc in this example) into strings *before* it is executed. You can use either CAST or CONVERT for this. I have always used CAST which has performed flawlessly, but that comes down to ignorance and luck. There may be a good / better reason to use CONVERT instead but as CAST has always worked for me I have been happy to stick with it for now.
>
>The VBA that passes the parameters is below - this works great now I have the darn append parameter lines in the correct sequence.
>
>'============================================================================================================
>Public Function BuildInOut_Summary_SPOnly_SSSP(Procedure As String, _
> SourceTable As String, _
> ProdClassType As Integer, _
> ProdClassName As Integer, _
> ProdName As Integer, _
> Activity As Integer, _
> MatClassName As Integer _
> ) As Boolean
>
>
> Dim cmd As New ADODB.Command
>
> On Error GoTo ErrHandle
>
> With cmd
> .ActiveConnection = gDbADOConStr
> .CommandType = adCmdStoredProc
> .CommandText = "spBuildInOut_Summary_SPOnly"
> End With
>
> With cmd.Parameters
> .Append cmd.CreateParameter("Procedure", adVarChar, adParamInput, 100, Procedure)
> .Append cmd.CreateParameter("SourceTable", adVarChar, adParamInput, 100, SourceTable)
> .Append cmd.CreateParameter("ProdClassType", adInteger, adParamInput, , ProdClassType)
> .Append cmd.CreateParameter("ProdClassName", adInteger, adParamInput, , ProdClassName)
> .Append cmd.CreateParameter("ProdName", adInteger, adParamInput, , ProdName)
> .Append cmd.CreateParameter("Activity", adInteger, adParamInput, , Activity)
> .Append cmd.CreateParameter("MatClassName", adInteger, adParamInput, , MatClassName)
> End With
>
> cmd.Execute
> Set cmd = Nothing
>
>Exit Function
>
>ErrHandle:
>
>BuildInOut_Summary_SPOnly_SSSP = False
>
>End Function
>'============================================================================================================
>
>So the functional SQL SERVER SPROC code looks like this now:
>
>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'
>BEGIN
> SELECT @SQL = @SQL + ' AND ProcedureName = ' + CAST(@ProdName as nvarchar(6)) + ''
>END
>
>If @Procedure = 'Downstream'
>BEGIN
> SELECT @SQL = @SQL + ' AND ProcedureName = ' + CAST(@ProdName as nvarchar(6)) + ''
>END
>-------------------------------------------------------------------------------------------------------
>
> If @Procedure = 'Stockpile'
>BEGIN
>SELECT @SQL = @SQL + ' AND ActivityFocus = ' + CAST(@Activity as nvarchar(6)) + ''
>END
>
> ELSE IF @Procedure = 'Discard'
>BEGIN
>SELECT @SQL = @SQL + ' AND ActivityFocus = ' + CAST(@Activity as nvarchar(6)) + ''
>END
>
> ELSE IF @Procedure = 'Rehandle'
>BEGIN
>SELECT @SQL = @SQL + ' AND FromActivity = ' + CAST(@Activity as nvarchar(6)) + ''
>END
>
> ELSE
>SELECT @SQL = @SQL + ' AND Activity = ' + CAST(@Activity as nvarchar(6)) + ''
>-------------------------------------------------------------------------------------------------------
>
> SELECT @SQL = @SQL + ' AND MaterialClassName = ' + CAST(@MatClassName as NVARCHAR(6)) + ''
>
>--PRINT @sql
>
> EXECUTE (@SQL)
>
>
>END
>
>'============================================================================================================
>
>Thanks everyone.
>
>Cheers
>Darryl
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>