mar.ko (9/2/2015)
I need to use the same SQL source code in more than one stored proc.Is there a way to -include mysql.sql or something like that ?
How does everyone handle this ? Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
Quick thought, you could turn this logic into a iTVF and call that function from the stored procedures.
😎
Pseudo iTVF function
CREATE FUNCTION [SCHEMA_NAME].[FUNCTION_NAME]
(
@PARAM_ONE [DATA_TYPE]
,@PARAM_TWO [DATA_TYPE]
,@PARAM_THREE [DATA_TYPE]
,@PARAM_FOUR [DATA_TYPE]
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
CASE WHEN @PARAM_ONE = 'Core' AND LEFT(@PARAM_TWO,3) = '00Q' THEN @PARAM_THREE ELSE 0 END AS CoreTotal
,CASE WHEN @PARAM_ONE = 'Core' AND LEFT(@PARAM_TWO,3) = '00Q' AND LEFT(@PARAM_FOUR,5)!='Upsel' THEN @PARAM_THREE ELSE 0 END AS CoreSales
,CASE WHEN @PARAM_ONE = 'Core' AND LEFT(@PARAM_TWO,3) = '00Q' AND LEFT(@PARAM_FOUR,5)='Upsel' THEN @PARAM_THREE ELSE 0 END AS CoreUpsell
,CASE WHEN @PARAM_ONE != 'Core' AND LEFT(@PARAM_TWO,3) = '00Q' THEN @PARAM_THREE ELSE 0 END AS NonCoreTotal
;